Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: Full Outer Join with 3 tables

    Okay, I have to perform a full outer join on three different tables:

    student (with attributes ID, name, dept_name, tot_cred) and the following data:
    '00128', 'Zhang', 'Comp. Sci.', '102'
    '12345', 'Shankar', 'Comp. Sci.', '32'
    '19991', 'Brandt', 'History', '80'
    '23121', 'Chavez', 'Finance', '110'
    '44553', 'Peltier', 'Physics', '56'
    '45678', 'Levy', 'Physics', '46'
    '54321', 'Williams', 'Comp. Sci.', '54'
    '55739', 'Sanchez', 'Music', '38'
    '70557', 'Snow', 'Physics', '0'
    '76543', 'Brown', 'Comp. Sci.', '58'
    '76653', 'Aoi', 'Elec. Eng.', '60'
    '98765', 'Bourikas', 'Elec. Eng.', '98'
    '98988', 'Tanaka', 'Biology', '120'

    instructor (with attributes ID, name, dept_name, salary) and the following data:
    '10101', 'Srinivasan', 'Comp. Sci.', '65000.00'
    '12121', 'Wu', 'Finance', '90000.00'
    '15151', 'Mozart', 'Music', '40000.00'
    '22222', 'Einstein', 'Physics', '95000.00'
    '32343', 'El Said', 'History', '60000.00'
    '33456', 'Gold', 'Physics', '87000.00'
    '45565', 'Katz', 'Comp. Sci.', '75000.00'
    '58583', 'Califieri', 'History', '62000.00'
    '76543', 'Singh', 'Finance', '80000.00'
    '76766', 'Crick', 'Biology', '72000.00'
    '83821', 'Brandt', 'Comp. Sci.', '92000.00'
    '98345', 'Kim', 'Elec. Eng.', '80000.00'

    advisor (with attributes s_ID and i_ID) and the data:
    '00128', '45565'
    '12345', '10101'
    '23121', '76543'
    '44553', '22222'
    '45678', '22222'
    '76543', '45565'
    '76653', '98345'
    '98765', '98345'
    '98988', '76766'

    Basically the resulting relation should have the name of the student and then the name of his advisor, and obviously since there are more instructors than students, some instructors will have null values for their respective students. Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by EonsNearby View Post
    ...obviously since there are more instructors than students...
    this is only obvious if it turns out that neutrinos can indeed actually travel faster than light

    there are 12 instructors and 13 students, and for the time being, the laws of the universe still insist that 12 is not more than 13

    are you sure you want a full outer join? why wouldn't a partial outer join actually give you what you want?

    by the way, when is this assignment due?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    4
    The assignment is due tomorrow, and I have to essentially replicate a full outer join.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this should help -- Simply SQL: The FROM Clause SitePoint

    read down to where is says that a full outer join is equivalent to the left outer join UNIONed with the right outer join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    4
    I have already seen something like this, and it does not really help my situation. This is something I came up with, but it keeps telling me that it does recognize x.s_ID as a column.

    Code:
    SELECT x.name, y.name
    
    FROM(
        SELECT student.name 
        
        FROM student LEFT OUTER JOIN advisor
        
        ON student.ID = advisor.s_ID 
        
        UNION 
        
        SELECT student.name 
        
        FROM student RIGHT OUTER JOIN advisor
        
        ON student.ID = advisor.s_ID) AS x
        
        LEFT JOIN(
            SELECT instructor.name 
            
            FROM instructor LEFT OUTER JOIN advisor 
            
            ON instructor.ID = advisor.i_ID UNION 
            
            SELECT instructor.name 
            
            FROM instructor RIGHT OUTER JOIN advisor 
            
            ON instructor.ID = advisor.i_ID) AS y
            
    ON x.s_ID = y.s_ID
    
    UNION
        
    SELECT x.name, y.name
    
    FROM (
            SELECT student.name 
            
            FROM student 
            
            LEFT OUTER JOIN advisor 
            
            ON student.ID = advisor.s_ID 
            
            UNION 
            
            SELECT student.name 
            
            FROM student 
            
            RIGHT OUTER JOIN advisor 
            
            ON student.ID = advisor.s_ID) AS x
            
            RIGHT JOIN( 
                SELECT instructor.name 
                
                FROM instructor LEFT OUTER JOIN advisor 
                
                ON instructor.ID = advisor.i_ID UNION 
                
                SELECT instructor.name 
                
                FROM instructor 
                
                RIGHT OUTER JOIN advisor 
                
                ON instructor.ID = advisor.i_ID) AS y
              
    ON x.i_ID = y.i_ID;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, that's way too complicated

    here's an idea ...

    do an inner join across all three tables, then UNION the student table, then UNION the instructor table, then put all that into a subquery in the FROM clause, and do a GROUP BY with MAXes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2011
    Posts
    4
    So how would I go about doing an inner join across three tables? Would I inner join two of them and then inner join the result with the third table? Also, I don't really see how your method is much less complicated than mine.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    /* ----- students with instructors --- */
    SELECT s.ID          AS s_id
         , s.name        AS s_name
         , s.dept_name   AS s_dept
         , s.tot_cred 
         , a.s_ID        AS x_s_ID
         , a.i_ID        AS x_i_ID
         , i.ID
         , i.name        AS i_id   
         , i.dept_name   AS i_name 
         , i.salary      AS i_dept 
      FROM student AS s
    INNER
      JOIN advisor AS a
        ON a.s_ID = s.ID
    INNER
      JOIN instructor AS i
        ON i.ID = a.i_ID
    /* ----- students without instructors --- */
    UNION ALL      
    SELECT s.ID          AS s_id
         , s.name        AS s_name
         , s.dept_name   AS s_dept
         , NULL
         , NULL
         , NULL
         , NULL
         , NULL
         , NULL
         , NULL
      FROM student AS s
    LEFT OUTER
      JOIN advisor AS a
        ON a.s_ID = s.ID
     WHERE a.s_ID IS NULL
    /* ----- instructors without students --- */
    UNION ALL
    SELECT NULL
         , NULL
         , NULL
         , NULL
         , NULL
         , NULL
         , i.ID
         , i.name        AS i_id   
         , i.dept_name   AS i_name 
         , i.salary      AS i_dept 
      FROM instructor AS i     
    LEFT OUTER
      JOIN advisor AS a
        ON a.i_ID = i.ID
     WHERE a.i_ID IS NULL
    didn't use the MAXes trick after all, substituted "not exists" LEFT OUTER JOINs with IS NULL checks
    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
  •