Lesson 32: Joining tables using Self Joins.
Now that's self-restraint. Waiting until Lesson 32 before the memes come out.
Sometimes a single data table will contain self referencing data points. This means we can use one field in the table to join to another field in the SAME table.
To do this we have to put the one source table on the left side and the same source table on the right side of the JOIN. But we have to give each of them a separate Table Alias.
Let's see an example - Watching The Detectives.
Table Name: DETECTIVES
So we have a table with a list of fictional TV detectives. Some have partners, some don't. We will write a SQL query now that only returns those who DO have a partner along with the partner's name.
We'll do this by doing a Self Inner Join on the DETECTIVES table back onto itself. We'll also use some of the Table Alias and Column Alias tricks we've learned in previous lessons.
SELECTa.ID as DetectiveID,a.Name as DetectiveName,b.ID as PartnerID,b.Name as PartnerNameFROM DETECTIVES as aINNER JOIN DETECTIVES as bON a.PartnerID = b.ID;
So Starsky's partner is Hutch and Hutch's partner is Starsky. Cagney's partner is Lacey and Lacey's partner is Cagney. And Luther had no partner so he drops out of the results altogether.
(And no I'm not really old enough to have watched either of those two first two TV shows when they originally aired.)
Relationships are as important to superheroes as they are to the rest of us mere mortals.
Use your TABLE ALIAS and COLUMN ALIAS knowledge to SELF JOIN the 'characters' table to itself and return two columns called 'character_name' and 'partner_name'.