SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: left overs

  1. #1
    Compulsive Clubber icky_bu's Avatar
    Join Date
    Aug 2003
    Location
    Portugal
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    left overs

    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.
    PHP Code:
    $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.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    Compulsive Clubber icky_bu's Avatar
    Join Date
    Aug 2003
    Location
    Portugal
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried the query?
    What did it return?

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    tombell's SQL finds artists that have no events.

    this finds events that have no artists:

    Code:
    SELECT
    	*
    	
    FROM events e
    
    LEFT JOIN events_artists ea ON
    	e.id = ea.eventid
    	
    WHERE
    	ea.event_id IS NULL

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    Compulsive Clubber icky_bu's Avatar
    Join Date
    Aug 2003
    Location
    Portugal
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  8. #8
    Compulsive Clubber icky_bu's Avatar
    Join Date
    Aug 2003
    Location
    Portugal
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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!
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •