Instruction
That's right! Aliases are also convenient in other cases. Let's analyze the following situation:
We want to put information about children and their mothers into a database. At some point, we would also like to show children together with their mothers using a JOIN.
Let's say we store both children and mothers in the same table Person. Each row has a column named MotherId. This column contains the ID of another row – the mother's row.
The question is: can we join the table Person with the table Person? The answer is simple: yes, we can! But you can't simply write this in your SQL query:
Person JOIN Person
You need to provide two different aliases for the same table:
SELECT * FROM Person AS Child JOIN Person AS Mother ON Child.MotherId = Mother.id;
Thanks to the aliases, the database engine will use the same table twice—the first time to look for children, and the second time to look for their mothers.
Exercise
We want to know who lives with the student Jack Pearson in the same room. Use a self join to show all information for Jack Pearson together with all information for each student living with him in the same room.
Remember to exclude Jack Pearson himself from the result!
Stuck? Here's a hint!
Type:
SELECT * FROM Student AS S1 INNER JOIN student AS S2 ON S1.RoomID = S2.RoomID WHERE S1.Name = N'Jack Pearson' AND S2.Name != N'Jack Pearson';
