zeke zeke - 1 year ago 56
SQL Question

MySQL: Get rows from another table as columns

I have the following two tables:


id name
1 bob
2 joe
3 paul


id user_id type name
1 1 car honda
2 1 computer mac
3 2 car toyota
4 2 computer pc
5 2 phone htc
6 3 car toyota
7 3 computer pc
8 3 phone samsung

I would like to create a query such that I get this as output:

user_id name car computer phone
1 bob honda mac
2 joe toyota pc htc
3 paul toyota pc samsung

Can I do this without using subqueries? Any help would be greatly appreciated, thanks!

Answer Source

If the list of types is fixed, you could use a query like this:

  max(case when p.type='car' then p.name end) as car,
  max(case when p.type='computer' then p.name end) as computer,
  max(case when p.type='phone' then p.name end) as phone
  possesions p inner join users u
  on p.user_id=u.id
group by

explanation: case when will return p.name when the condition is true, and null otherwise. Here we are grouping by user_id and name, and max() will return the maximum non-null value per each group.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download