Sunday, February 14, 2016

SQL - Columns in two tables showing the same value when queried

Consider a database storing the details of all schools and students in a city and the information of which student is in which school.
One way to model this is using the following tables:
Schools:
SchoolID Name MaxIntake
1 Knowledge School 1200
2 Wisdom School 670
Students:
StudentID Name SchoolID
1 Ram 2
2 Peter 1
3 Zaki 2

As is evident from the names, SchoolID in the table Students is a foreign key referring to the primary key SchoolID in the Schools table.
Now, if you want the list of all the students and their schools, the query would be:
select studentid, name, name from schools, students where schoolid = studentid;
But this query will show the same name for both the student and the school. Why?

I will try to explain this without going into the specifics of implementation of the specific DB engine. The part of the query from schools, students where schoolid = studentid will create a temporary table, like this:

SchoolID Name MaxIntake StudentID Name SchoolID
2 Wisdom School 670 1 Ram 2
1 Knowledge School 1200 2 Peter 1
2 Wisdom School 670 3 Zaki 2

This table has two fields named as name and when you query the value of name, you get the value from one of them.
To fix this, you need to provide an alias for the table names in the query and use them:
select studentid, st.name, sc.name from schools sc, student st where st.schoolid = sc.schoolid;
Now the table that would be created would be like:
sc.SchoolID sc.Name sc.MaxIntake st.StudentID st.Name st.SchoolID
2 Wisdom School 670 1 Ram 2
1 Knowledge School 1200 2 Peter 1
2 Wisdom School 670 3 Zaki 2

Since there is no conflict in the field names, the output would be fine.
Post a Comment