CODEWITHSUNDEEP
sqlselect
user007
user007

Reputation: 1084

SQL SELECT from same tables two times

Whats the best way to select names two times from a table

NAME table
Id  Name
1   john
2   zoe
3   joe
4   abby

COUPLE table
NameId NameId2
1      2
3      4

Result should look

Name  Name
john  zoe
joe   abby

I would do something like this

SELECT NAME.Name, NAME2.Name 
FROM COUPLE, NAME, NAME AS NAME2 
WHERE NAME.Id=COUPLE.NameId 
AND NAME2.Id=COUPLE.NameId2

Is this the best way to get the result? (for performance)

Upvotes: 0

Views: 189

Answers (2)

PM 77-1
PM 77-1

Reputation: 13334

SELECT n1.Name, n2.Name
FROM Couple C 
JOIN Name n1 ON (n1.id = c.nameID)
JOIN Name n2 ON (n2.id = c.nameID2)

Upvotes: 1

Matt Busche
Matt Busche

Reputation: 14333

My biggest recommendation is to use explicit joins and also scope your second name in the SELECT list

SELECT NAME.Name, NAME2.Name Name2
FROM COUPLE
 INNER JOIN NAME ON NAME.Id=COUPLE.NameId 
 INNER JOIN NAME AS NAME2 ON NAME2.Id=COUPLE.NameId2

Upvotes: 1

Related Questions