I think you want to try 'having mo.date IS NULL' after the order by.
Daren wrote:
I'm query for a list of offers from a table, but am trying to do a Left Join on the table that keeps track of which members have completed which offers (so that the query will not return offers that the member has already completed).
Query:
select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') order by ol.weight desc limit 3;
Results:
+----+------------+ | id | date | +----+------------+ | 1 | 2004-06-04 | | 2 | NULL | | 3 | NULL | +----+------------+
So, member_id 1 has completed offer_id 1, but not offer_id's 2 or 3.
I would assume that I simply need to add one more where clause to only return results with a non-null date. I tried:
select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NULL) order by ol.weight desc limit 3;
However, this does not work - I get an empty result.
Which is *really* weird, because if I change the query to only return non-null values, like so:
select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NOT NULL) order by ol.weight desc limit 3;
It works perfectly:
+----+------------+ | id | date | +----+------------+ | 1 | 2004-06-04 | +----+------------+
Of course, this is the opposite of what I want, so I'm quite confused. Can anyone point me in the right direction?
TIA!
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
