FC3D FC3D - 6 months ago 7
SQL Question

Select Data from three SIMPLE JOIN Tables tblA, tblB and tblC

I want to Select Data from three SIMPLE JOIN Tables tblA, tblB and tblC where tblA has Foreign key of tblB and tblC and tblC also has Foreign key of tblB

The Tables are as such :

tblB

CountryID | Country
-----------|---------
1 | England
2 | Wales
3 | Scotland
4 | Ireland


tblC

StudentID | CountryID | Student
-----------|-----------|-------------
1 | 1 | ABC-Student
2 | 2 | XYZ-Student


tblA

SchoolID | CountryID | StudentID | School
-----------|-----------|-----------|-------------
1 | 3 | 1 | ABC-School
2 | 4 | 2 | XYZ-SChool


Now, I need to select the data from tblA to produce the following :

SchoolID | School | Country | Student | Student Country
---------|------------|----------|-------------|----------------
1 | ABC-School | Scotland | ABC-Student | England
2 | XYZ-SChool | Ireland | XYZ-Student | Wales


I have tried these following query but can not get perfect result:

SELECT tblA.SchoolID,
tblA.School,
tblB.Country,
tblC.Student,
tblB.Country AS [Student Country]
FROM tblA, tblB, tblC
WHERE tblA.CountryID = tblB.CountryID
AND tblA.StudentID = tblC.StudentID
AND tblC.CountryID = tblB.CountryID
ORDER BY tblA.SchoolID ASC;


It's Returning Nothing because Student and School has different Country
if i remove

AND tblC.CountryID = tblB.CountryID


its return this result:

SchoolID | School | Country | Student | Student Country
---------|------------|----------|-------------|----------------
1 | ABC-School | Scotland | ABC-Student | Scotland
2 | XYZ-SChool | Ireland | XYZ-Student | Ireland


Here School Country and Student Country are same which is not right,
Hope this makes sense.

I want SIMPLE JOIN to solve this problem if it can be!
If anyone could help, would be much appreciated.

Answer

You should join the country table twice, one time to get the student country and another to get the school country. Also, as jarlh already told you, you should switch to the JOIN syntax.

WITH tblB AS (
  SELECT 1 CountryID, 'England' Country FROM dual
  UNION
  SELECT 2 CountryID, 'Wales' Country FROM dual
  UNION
  SELECT 3 CountryID, 'Scotland' Country FROM dual
  UNION
  SELECT 4 CountryID, 'Ireland' Country FROM dual
), tblC AS (
  SELECT 1 StudentID, 1 CountryID, 'ABC-Student' Student FROM dual
  UNION
  SELECT 2 StudentID, 2 CountryID, 'XYZ-Student' Student FROM dual
), tblA AS (
  SELECT 1 SchoolID, 3 CountryID, 1 StudentID, 'ABC-School' School FROM dual
  UNION
  SELECT 2 SchoolID, 4 CountryID, 2 StudentID, 'XYZ-SChool' School FROM dual
)
SELECT a.SchoolID, a.School, b.Country, c.Student, b2.Country     Student_Country
FROM tblA a 
JOIN tblB b ON b.CountryID = a.CountryID
JOIN tblC c ON c.StudentID = a.StudentID
JOIN tblB b2 ON b2.CountryID = c.CountryID