CODEWITHSUNDEEP
sqljoinself-joinansi-sql
Gonzalo.-
Gonzalo.-

Reputation: 12682

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

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!

Upvotes: 2

Views: 554

Answers (3)

Rohan Nayak
Rohan Nayak

Reputation: 233

This will give you the results in format you want. 

SELECT TAB1.ID,TAB2.ID
FROM 
(
SELECT * FROM people T1
 WHERE fatherID IN ( SEL T1.ID FROM people T1 INNER JOIN  people T2 
ON(     T1.id=T2.fatherID) WHERE T1.fatherID IS NOT NULL GROUP BY 1) ) TAB1

INNER JOIN 
(
SELECT * FROM people T1
WHERE fatherID IN ( SEL T1.ID FROM people T1 INNER JOIN  people T2 
ON(   T1.id=T2.fatherID)WHERE T1.fatherID IS NOT NULL GROUP BY 1) ) TAB2

ON( TAB1.fatherID<>TAB2.fatherID)
GROUP BY 1,2
WHERE TAB1.ID <TAB2.ID;

Upvotes: 0

Amogh Huilgol
Amogh Huilgol

Reputation: 1282

SELECT T1.id , T2.id FROM 
(
    SELECT A.id,A.fid FROM family A 
    WHERE a.fid IN 
    ( 
        SELECT id FROM family
        WHERE fid IN (SELECT id FROM family WHERE fid IS NULL)
    )
)T1

JOIN
(
     SELECT A.id,A.fid FROM family A 
     WHERE a.fid IN 
     ( 
          SELECT id FROM family
          WHERE fid IN (SELECT id FROM family WHERE fid IS NULL)
     )
)T2
ON t1.fid<>t2.fid
AND t1.id<t2.id

Upvotes: 0

user1460819
user1460819

Reputation: 2112

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

Upvotes: 3

Related Questions