CODEWITHSUNDEEP
sql
Sri Test
Sri Test

Reputation: 387

SQL Query to form duo pairs

I have a table with three fields id, name and no. of years. I need to form a table of duos of last names based on the condition if no. of years of a person is greater than the other, they should form a pair. I tried the following query. But it returns no rows. Why?

select s1.LastName as "coach",s2.LastName as "Trainee" 
from Members s1 
     join Members s2 on s1.SID=s2.SID 
where s1.Years>s2.Years; 

SID Name Years
1   Leo   4
2   Aldrin 1
3   Niks  5
4   Smith 1


Output
Leo  Aldrin
Leo  Smith
Niks Leo
Niks Aldrin
Niks Smith

Upvotes: 0

Views: 166

Answers (2)

lukaszberwid
lukaszberwid

Reputation: 1177

You can also use cross join to do that:

SELECT
    s1.LastName AS "coach",
    s2.LastName AS "Trainee"
FROM Members s1, Members s2
WHERE s1.SID <> s2.SID AND s1.Years > s2.Years;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

You should be using an inequality on the check on SID:

SELECT
    s1.LastName AS "coach",
    s2.LastName AS "Trainee"
FROM Members s1
INNER JOIN Members s2
    ON s1.SID <> s2.SID AND   -- this is maybe not even necessary here, by the way
       s1.Years > s2.Years;

The above query says to find all coach/trainee pairs, being defined as the former having more years than the latter. The reason your initial query was not returning any records is that only one record will have a given SID identifier, and by definition its Years value will also be the same.

Upvotes: 1

Related Questions