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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Jim Horn
how come it is different than mine :)
you are toooo slow ha ha...
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
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
Open in new window