[Resolved] Finding nonmatching entries

[bunner_bob]

I have two tables - one of events, the other of dates.

Each event has an id (number)

Each date has an id (unique number that is not the event id), a field that says what kind of date it is (event, news, etc.) and a 'localid' field that is the same as the event id it is linked to.

So an event might have:
id = 12
title = An event

And there might be a couple of dates with:
id = 77
datetype = event
localid = 12

id = 23
datetype = news
localid = 12

Obviously in this case it's fairly straightforward to find an event's date:

$sql = mysql_query("SELECT * FROM events, dates WHERE events.id = dates.localid AND dates.datetype = 'event')

But - let's say I have an event with no associated date, and a date entry that's a news date but has the same localid as the event (news items exist in a separate table and so there can easily be an event and a news item with the same id)

Our event is still:
id = 12
title = An event

But now there's a news date with the event's id, but no event date with that id:
id = 23
datetype = news
localid = 12

I need some sort of select that looks at each event, checks the date table for 'event dates' and if there are none, the event is added to the select results. Sort of a negative select. I need to list all the events that don't have dates assigned to them yet.

Any ideas on how I might word this?

Thanks in advance,
Bob

[tomhath]

It depends on what dbms you are using. Most (including mysql 4.x) let you use a NOT EXISTS clause:

select * 
from events 
where not exists
   (select * from dates  

    WHERE events.id = dates.localid      

         AND dates.datetype = 'event')

If you are using mysql 3.2x you need to fake the NOT EXISTS by using a LEFT JOIN (more details in the mysql docs). Looks funny but it works.

select events.*
from events
left join dates
on events.id = dates.localid AND dates.datetype = 'event'
where dates.localid is null

[bunner_bob]

Using 3.2.x - and that's the ticket - thanks!