CODEWITHSUNDEEP
sqlinner-joinself-join
yorwosA
yorwosA

Reputation: 11

Inner workings of a self join combined with a WHERE

I have a table

contacts
---------
contact_id  company         contact
1000000001  Village Toys    John Smith
1000000002  Kids Place      Michelle Green
1000000003  Fun4All         Jim Jones
1000000004  Fun4All         Denise L. Stephens
1000000005  The Toy Store   Kim Howard

I Want to find all the contacts of the company that Jim Jones works for. This is the correct set.

1000000003  Fun4All         Jim Jones
1000000004  Fun4All         Denise L. Stephens

I used a subquery to work this out and it works I got the correct results. I tried then a self inner join with a where on 'Jim Jones' and I get results I cannot understand.

SELECT tableA.contact_id, tableA.company, tableA.contact
FROM company AS tableA
JOIN company AS tableB
ON tableA.company = tableB.company
WHERE tableB.contact = 'Jim Jones'

I get the correct result set

1000000003  Fun4All         Jim Jones
1000000004  Fun4All         Denise L. Stephens

But If I change the WHERE with 

WHERE tableA.contact = 'Jim Jones'

I get

1000000003  Fun4All         Jim Jones
1000000003  Fun4All         Jim Jones

Why does it do that? How exactly does this works? I spend the whole night trying to figure it out. But it eludes me. The examples I found on Google couldn't make me undestand.

The database (In my question i changed the column names for clarification)

http://forta.com/books/0672336073/TeachYourselfSQL_SQLite.zip
http://forta.com/books/0672336073/TeachYourselfSQL_Oracle.zip
http://forta.com/books/0672336073/TeachYourselfSQL_MySQL.zip
http://forta.com/books/0672336073/TeachYourselfSQL_MicrosoftSQLServer.zip
http://forta.com/books/0672336073/TeachYourselfSQL_Access2007.zip
http://forta.com/books/0672336073/TeachYourselfSQL_OpenOfficeBase.zip

Upvotes: 0

Views: 78

Answers (2)

wosi
wosi

Reputation: 483

To get an explanation do this:

1) make the other table (self join) visible to see the result you actually get when joining without the where clause:

select A.contactid, A.company, A.contact, B.contactid, B.company, B.contact from contacts A inner join contacts B on A.company = B.company

self joined tables

2) now add your WHERE clause and exchange A.contact with B.contact to see what happens

Upvotes: 2

DxTx
DxTx

Reputation: 3357

You can use this way to get the desired result.

SELECT *
FROM   TableName
WHERE  company IN (SELECT company
                   FROM   TableName
                   WHERE  contact = 'Jim Jones')

In your query, if you're changing WHERE tableB.contact = 'Jim Jones' to WHERE tableA.contact = 'Jim Jones', then you have to change the SELECT statement too.

SELECT tableA.contact_id, tableA.company, tableA.contact
FROM company AS tableA
JOIN company AS tableB
ON tableA.company = tableB.company
WHERE tableB.contact = 'Jim Jones'

SELECT tableB.contact_id, tableB.company, tableB.contact
FROM company AS tableA
JOIN company AS tableB
ON tableA.company = tableB.company
WHERE tableA.contact = 'Jim Jones'

You have to do this because you are filtering from this result.
(You are doing a self-join using company column.)

SELECT *
FROM company AS tableA
JOIN company AS tableB
ON tableA.company = tableB.company

+-----------------+----------------+---------------------+----------------+----------------+---------------------+
| contact_id (A)  |  company (A)   |     contact (A)     | contact_id (B) |  company (B)   |     contact (B)     |
+-----------------+----------------+---------------------+----------------+----------------+---------------------+
|      1000000001 | Village Toys   | John Smith          |     1000000001 | Village Toys   | John Smith          |
|      1000000002 | Kids Place     | Michelle Green      |     1000000002 | Kids Place     | Michelle Green      |
|      1000000003 | Fun4All        | Jim Jones           |     1000000003 | Fun4All        | Jim Jones           |
|      1000000004 | Fun4All        | Denise L. Stephens  |     1000000003 | Fun4All        | Jim Jones           |
|      1000000003 | Fun4All        | Jim Jones           |     1000000004 | Fun4All        | Denise L. Stephens  |
|      1000000004 | Fun4All        | Denise L. Stephens  |     1000000004 | Fun4All        | Denise L. Stephens  |
|      1000000005 | The Toy Store  | Kim Howard          |     1000000005 | The Toy Store  | Kim Howard          |
+-----------------+----------------+---------------------+----------------+----------------+---------------------+

Upvotes: 1

Related Questions