Lesson 32: Joining tables using Self Joins.

By Alan Hylands — 2 minute read

Spiderman pointing at Spiderman

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

IDNamePartnerID
1Starsky2
2Hutch1
3Cagney4
4Lacey3
5LutherNULL

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.

SELECT
a.ID as DetectiveID,
a.Name as DetectiveName,
b.ID as PartnerID,
b.Name as PartnerName

FROM DETECTIVES as a
INNER JOIN DETECTIVES as b
ON a.PartnerID = b.ID;

Results:

DetectiveIDDetectiveNamePartnerIDPartnerName
1Starsky2Hutch
2Hutch1Starsky
3Cagney4Lacey
4Lacey3Cagney

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.)

Quiz Time.

The Question.

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'.

The Data.

The Editor.


Run SQL


Show Answer

The Answer.

Correct SQL:

                    SELECT a.name as character_name, b.name as partner_name FROM characters a INNER JOIN characters b ON a.partner_id=b.id;
                    

Correct Output:

Try the next lesson