[thelist] using SQL left join query and NULL to find records NOT in a table
Ben Phillips
ben at inchima.com
Fri Feb 15 06:35:00 CST 2002
i am using MySQL to find a list of users who haven't been sent a newsletter.
the tables are:
=========
USER
userid | firstname | email | sendnewsletter
NEWSLETTER
newsletterid | subject | email
NEWSLETTER_SENT
newsletterid | userid
=========
users are stored in USER. newsletters are stored in NEWSLETTER. if a user
has selected to receive newsletters, sendnewsletter is set to 1, otherwise
it is 0.
when a newsletter is sent, the newsletterid and userid are put into
NEWSLETTER_SENT. so if USER 'benji' is sent NEWSLETTER '4' then
NEWSLETTER_SENT will contain '4 | benji'
i want to run a query that returns all USERs who *haven't* received a
NEWSLETTER with newsletterid='$n'.
i can use the following query to find all USERs who have not received any
NEWSLETTERs:
=========
select * from USER
LEFT JOIN NEWSLETTER_SENT
ON USER.userid = NEWSLETTER_SENT.userid
WHERE NEWSLETTER_SENT.userid is NULL
=========
however, i want to return users who haven't been sent a NEWSLETTER with a
newsletterid of $n.
any ideas? anything to stop this frustration, please... :o)
of course, it's MySQL and i don't have temporary tables of sub-selects...
benji
inchima.com
More information about the thelist
mailing list