Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Selecting from 3 databases

    Hello Everyone! I've been working on this specific statement for a while now and all my attempts are failing. What I'm basically trying to do is create a statement that shows the names of athletes who are NOT playing in any team belonging to a specific school. There are two schools with four sports each. So I have created tables for Athletes, the sports they play, the number of sports they are involved in and the school they are enrolled in. Below is the statement I came up with but I'm having difficulty with it excluding an athlete that is a part of a team in both schools. In this case I want to show the names of athletes who are NOT playing in any sport that belongs to the North Springs school, so basically joining 4 tables. Any assistance I'll be grateful for so I can create the rest of my database. Thanks!

    SELECT A.Name FROM Sport JOIN Playing P ON (P.Sport = S.Id) JOIN Athlete A ON (P.Athlete=A.Id) JOIN School Sc ON (S.School=Sc.Id) WHERE Sc.Name != ‘North Springs’;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT A.Name 
      FROM Athlete A 
    LEFT OUTER 
      JOIN Playing P 
        ON P.Athlete = A.Id
    LEFT OUTER
      JOIN Sport S
        ON S.Id = P.Sport 
    LEFT OUTER
      JOIN School Sc 
        ON Sc.Id = S.School
       AND Sc.Name = 'North Springs'
     WHERE Sc.Name IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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