Problems with SQL statement

[adamthenewbie]

ok I have this SQL statement

SELECT c.title FROM calendar c, onlineReg_meet_info m WHERE c.id = m.calendar_id

This displays all the calendar title that are both in onlineReg_meet_info and calendar

Basically I am trying to write a SQL statment to do the opposite. I want to display the titles of the calendar events in calendar that are NOT in onlineReg_meet_info

How would I go about this?

 

[adamthenewbie]

I have been looking around and you would think this would work.

SELECT c.id, c.eventMonthDay, c.eventYear, c.title, c.location, c.ordering FROM calendar c WHERE c.id NOT IN (SELECT m.calendar_id FROM onlineReg_meet_info m) ORDER BY c.ordering ASC

But it doesn't. Any help?

 

[Glenn9999]

OK, you have to look at SET theory...

The JOIN is what is doing this for you...

Basically what you are wanting is:

SELECT c.title FROM 
  calendar c LEFT JOIN onlineReg_meet_info m 
   ON  c.id = m.calendar_id 
 WHERE M.CALENDAR_ID IS NULL;

 

[dnoeth]

What do you mean by "NOT IN not working"?
The query is ok, but maybe you got a NULL in calendar_id...

Rewrite it as a NOT EXISTS, this always works and is usually faster than NOT IN:

select *
from calendar c
where not exists
(select * from onlineReg_meet_info m
where m.calendar_id = c.id)

Dieter