[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