| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-12-11, 14:11
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 4
|
|
|
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.
|
|
10-12-11, 15:21
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by EonsNearby
...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?
|
|
10-12-11, 15:43
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 4
|
|
|
|
The assignment is due tomorrow, and I have to essentially replicate a full outer join.
|
|
10-12-11, 16:58
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
|
|
10-12-11, 17:18
|
|
Registered User
|
|
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;
|
|
10-12-11, 20:03
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
10-12-11, 22:23
|
|
Registered User
|
|
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.
|
|
10-14-11, 10:53
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Full Outer Join with 3 tables
