SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast runrobrun's Avatar
    Join Date
    Jan 2004
    Location
    Leighton, Iowa
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple table Join and show missing data

    I have spent about two hours testing various SQL calls to pull data from three tables and check for missing data.

    Here are my three tables:

    table: athletes:
    fields: f_name, l_name, year, a_id (primary Key), photo, gender, active

    table: results:
    fields: id (primary key), a_id (foreign key athletes table), s_id (foreign key schedule table), time, place, dnr

    table: schedule:
    fields, s_id (primary key), location, time, date

    I want to be able to pull all athletes who have yet to have a results for a given scheduled event.

    I can pull athletes that have entries for the event easily enough but I need the opposite.

    The following pulls all the athletes and registers any that have an entry in results:
    Code:
    SELECT  * 
    FROM (athlete
    LEFT  JOIN results ON athlete.a_id = results.a_id
    )
    LEFT  JOIN schedule ON results.m_id = schedule.s_id
    But when I want to check against a specific event and look for athletes that don't have entries.
    Rob Hammann
    ArrowQuick : Vennard College : William Penn University
    "Sometimes I lie awake at night, and I ask,
    'Where have I gone wrong?'
    Then a voice says to me,
    'This is going to take more than one night.' "
    Charlie Brown.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code sql:
    SELECT *
      FROM athlete
     WHERE a_id NOT IN ( SELECT r.a_id
                           FROM results r
                         INNER
                           JOIN schedule s
                             ON s.s_id = r.s_id 
                          WHERE r.id = 12345 );

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh and you should be using INNER JOINs in your first query if you want to return only athletes who have an entry in the results table.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT  * 
    FROM athlete
    CROSS JOIN schedule
    LEFT OUTER JOIN results 
    ON  athlete.a_id = results.a_id
    AND results.m_id = schedule.s_id
    AND results.id = specific even id
    WHERE results.a_id IS NULL

  5. #5
    SitePoint Enthusiast runrobrun's Avatar
    Join Date
    Jan 2004
    Location
    Leighton, Iowa
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most excellent...that worked. Don't know why I didn't think about NOT IN. I haven't used that clause in a long time.
    Rob Hammann
    ArrowQuick : Vennard College : William Penn University
    "Sometimes I lie awake at night, and I ask,
    'Where have I gone wrong?'
    Then a voice says to me,
    'This is going to take more than one night.' "
    Charlie Brown.


Tags for this Thread

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
  •