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]
