Need help with unknown column in where clause error

Databases
1

Any idea why I’m getting the error of, “Unknown column ‘ID’ in where clause” when I don’t even mention ID in the where clauses at all? This really has me stumped! Especially since the code works fine when I run it in Querious for the Mac.

Thanks!

SELECT uID as ID
	, m.dateAdded AS sortDate
	, concat(firstName, ' ', lastName) as senderName
	, m.type
	, m.message
	, 'Online' as source
	, case when dateDiscovered is not null then 'Yes' else 'No' end as discovered
	, case when dateViewed is not null then 'Yes' else 'No' end as viewed
	, upgradeNeeded
	FROM users u
	INNER JOIN messagesOnline m
	ON u.uID = m.senderID
WHERE date(dateAdded) = '$statDate'
UNION
SELECT 'ID' as ID
	, dateAdded AS sortDate
	, concat(firstName, ' ', lastName) as senderName
	, type
	, message
	, 'Offline' as source
	, case when dateDiscovered is not null then 'Yes' else 'No' end as discovered
	, case when dateViewed is not null then 'Yes' else 'No' end as viewed
	, upgradeNeeded
	FROM messagesOffline
WHERE date(dateAdded) = '$statDate'
ORDER by sortDate desc
2

That’s odd - it sounds almost like your sql engine is trying to apply the union along with the where clause.

Perhaps, as an off the wall test, wrap the two selects with parenthesis?

3

yes, i have an idea – the query you posted isn’t the same one that produced the error message

run the query outside of php to see :slight_smile:

4

[quote=“DaveMaxwell, post:2, topic:100370, full:true”]Perhaps, as an off the wall test, wrap the two selects with parenthesis?
[/quote]please, no need to get all hackish on us :wink:

5

:stuck_out_tongue_closed_eyes: (closest I can get to a raspberry).

I didn’t mean it that way, but if it worked, it’s an issue with the db layer/engine being used, not in the query itself.