Search code examples
sqljoinself-joinansi-sql

Query on table joined with itself

Today I have a final Exam. I approved, happily :D but one of the problems is really blowing my mind.

I need help, so I can rest in peace.

THE PROBLEM

We have a table "People"

(PK)id | name | fatherID
    ---------------------
    1  | gon  |   2
    2  | cesar|   6
    3  | luz  |   2
    4  | maria|   5
    5  | diego|   6
    6  | john |  -

this is only an example of data. This table has a relation with itself, on table fatherId(FK) with table id(PK) I need to do a query that show me 2 columns, in one the name of a person, and in the another one, his/her cousin.

Pretty simple until here, right? The problem is that I have some restrictions

  • ONLY ANSI allowed. NO T-sql, or another one. Also, ANSI 99 standard, not 2003 or higher
  • subquerys are not allowed. And the worst:
  • NO relations repeated.

For example, considering in this example, gon and maria are cousins. If I show, gon | maria in the results, I can't show maria | gon.

SO, how I can do this? Is really burning my head.

What I tried?

Well, the big problem was in the last requisite, the repetition of data. Ignoring that, I put this on my exam (knowing is wrong..)

select p3.name as OnePerson, p4.name as Cousin
from
people p1
inner join people p2 on p1.fatherid = p2.fatherid and p1.id != p2.id
inner join people p3 on p1.id = p3.fatherid
inner join people p4 on p1.id = p4.fatherid

of course, this is not solving the last requeriment, and I have a 4 in the test(we pass with 4) but anyway, my head is burning. So please, help me!

Another options explored

one of my friends, that also had the same exam said me

"Well, considering every relation is duplicated, I can use top count(*) and an order by and get the half correct"

but.. Top is not ANSI!

Solution

  • You can add to your query WHERE p3.id < p4.id. This will eliminate duplicate results like gon | maria and maria | gon.