Link to home
Start Free TrialLog in
Avatar of Member_2_1242703
Member_2_1242703

asked on

SQL: Pulling all records from one table using join data from 2 other tables

I have 3 tables: tblStudents, tblHighSchools, and tblColleges

tblHighSchools and tblColleges both have only 2 columns (ID, SchoolName)

tblStudents has 4 columns: StudentName, GradYear, HighSchool, and College (Where high school and college will be an int that exists in the corresponding table)

I want to select StudentName, GradYear, SchoolName (Where tblStudents.HighSchool = tblHighSchools.ID), and SchoolName (Where tblStudents.College = tblColleges.ID)

None of the columns will have empty data in tblHighSchools or tblColleges, but the HighSchool and College columns will have NULL values in some of the records in tblStudents. I need to pull every single record in tblStudents

How do I do this? I've tried a few different join combinations, but I keep getting records left out.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn
For starters give this a whirl.  The LEFT JOIN means that it will return all rows in tblStudents, and the values in other tables if they exist, otherwise NULL.
SELECT s.StudentName, s.GradYear, hs.SchoolName, c.SchoolName
FROM tblStudents s
   LEFT JOIN tblHighSchools hs ON s.HighSchool = tblHighSchools.ID
   LEFT JOIN tblColleges c ON s.College = tblColleges.ID

Open in new window

@Jim Horn

how come it is different than mine :)
you are toooo slow ha ha...
{{  Apparently I must drink more coffee in the morning before answering questions }}
and in your qry, we will get 2 SchoolName columns, and maybe one of them will be renamed by DB as ShoolName[1]

if not, it may create issues if we try to use it in another query as subselect, or it may create issues if we try to consume in applications...

thats why I renamed that column as CollageName