pm100 pm100 - 7 days ago 9
SQL Question

select statment that has columns made from different rows of a joined table

2 tables

user cols = name,email

joe|joe@gmail.com
sara|sara@hotmail.com
alan|alan@gmail.com


table 2 is extras cols = name,col,value

joe|favoritecolor|blue
joe|boss|sam
sara|favoritecolor|green


(really extending the schema of the first table but not so that the sql engine notices it)

To get peoples favoritecolor

Select name
, value As favoritecolor
From user
, extras
Where extras.name = user.name
And extras.col = 'favoritecolor'


But how do I get favoritecolor and boss?

Note the output I want is

joe|joe@gmail|blue|sam
sara|sara@hotmail.com|green|<null>
alan|alan@gmail.com|<null>|<null>

Answer

You can use double join on the same table using alias

(for join use inner join if the keys always are present and match otherwise use left join)

 select a.name, e1.value  as favoritecolor , e2.value as boss
 from user
 INNER JOIN extras as e1 on e1.name = user.name and e1.col = 'favoritecolor'
 INNER JOIN extras as e2 on e2.name = user.name and e2.col = 'boss'

If your col don't match always you should use left join

 select a.name, e1.value  as favoritecolor , e2.value as boss
 from user
 left JOIN extras as e1 on e1.name = user.name and e1.col = 'favoritecolor'
 left JOIN extras as e2 on e2.name = user.name and e2.col = 'boss'

if you have always a favorite color relation but not always a boss

 select a.name, e1.value  as favoritecolor , e2.value as boss
 from user
 Inner JOIN extras as e1 on e1.name = user.name and e1.col = 'favoritecolor'
 left JOIN extras as e2 on e2.name = user.name and e2.col = 'boss'