left overs

[icky_bu]

I have three tables:

- artists
- events
- events_artists (many-to-many)

So, If I where to get a list of all the artists linked to a certain event and also get a list of those NOT linked to that event, I'd guess a left outer join would be needed, right?


ex:
artists
id - name
1 - joe
2 - bob
3 - jack
4 - john

events
id - title
1 - bigparty
2 - smallparty

events_artists
eventid - artistid
1 - 2
1 - 3
1 - 4
2 - 1
2 - 3


I know how to get the artists related to a certain event.

$sql = "SELECT art.*, ea.artist_id FROM artists as art, events_artists as ea WHERE ea.artist_id = art.id AND event_id = $eid"; 

But how would I get the artists NOT linked to a certain event? For example, event with ID = 2?


I hope I explained myself properly. Any help is well apreciated.

[tombell]

Try this
SELECT a.name FROM artists a LEFT JOIN events_artists ea ON a.id=ea.artistid LEFT JOIN events e ON e.id=ea.eventid AND e.id=2 WHERE e.id IS NULL

[icky_bu]

you set e.id IS NULL, but I want to find where there is no artist set for a certain event.
or am I mistaking your sql?

[tombell]

Have you tried the query?
What did it return?

[longneck]

tombell's SQL finds artists that have no events.

this finds events that have no artists:

SELECT
	*
	
FROM events e

LEFT JOIN events_artists ea ON
	e.id = ea.eventid
	
WHERE
	ea.event_id IS NULL

[tombell]

SELECT a.name FROM artists a LEFT JOIN events_artists ea ON a.id=ea.artistid AND eventid=2 WHERE eventid IS NULL

will give artists not linked to eventid 2

[icky_bu]

tombell's last solution works like a charm, even though it doesnt make too much sense to me semantically.

tombell, would you mind breaking it down just a bit. I'm sorry... maybe just a quick explanation... I'd appreciate it. Thanks!

[icky_bu]

If I'm understanding it properly, it's checking where artist_id in events_artists has a NULL event_id.
As opposed to looking for the NULL artist_id's the event_id is SET. Right?

[tombell]

tombell's last solution works like a charm, even though it doesnt make too much sense to me semantically.

tombell, would you mind breaking it down just a bit. I'm sorry... maybe just a quick explanation... I'd appreciate it. Thanks!

It is a matter of how left join works

The query retrieves all names from artists where there is a relationship on events_artists. Additionally, by adding AND eventid=2, the join fills with NULL values each events_artists.eventid where there is no match for an artistid.

I hope I helped you understand it.