mysql select multiple columns of one row from another table

Question

I usually google my questions, since they are not unique only to me. However, this time I can't find answer (probably not using right keywords).

Problem description:

I have two tables.
First table - defenition_list holds unique ID and Name

ID | NAME   
-----------
 1 | BMW   
 2 | AUDI  
 3 | VW  
 4 | MAZDA

Second Table - used_id holds ID from first table

ID | def_uid1 | def_uid2 | def_uid3  
------------------------------------
 1 |    2     |     3    |    2    
 2 |    4     |     2    |    1

So usually If I need to return value of just one column, I would make select like:

SELECT d.NAME, u.def_uid1 FROM defenition_list d, used_id u WHERE d.ID=u.def_uid1

But how to write the query to get names of def_uid2 and def_uid3 at same time? So result would look like:

ID | def_uid1 | def_uid2 | def_uid3 |
--------------------------------------
 1 |  AUDI    |   VW     |    AUDI  |
 2 |  MAZDA   |   AUDI   |    BMW   |

that depends on what you want, do you want to join the two tables based on the equality of defenition_list.ID and any/all of the columns in used_id or what? — Abdelrhman Adel
– Abdelrhman Adel, Commented Jun 1, 2015 at 14:51
sqlfiddle.com/#!9/a6973/2 - I assumed u wanted to list into database the top 3 car brand of the user. U could additional User TABLE to be correct & set user_pref table primary_key (user_id, id_brand). Hope it helped. — Falt4rm
– Falt4rm, Commented Jun 1, 2015 at 15:05

Marc B · Accepted Answer · 2015-06-01 14:52:26Z

3

You'd have to join your names table 3 times:

SELECT used_id.ID, d1.name, d2.name, d3.name
FROM used_id
LEFT JOIN definition_list AS d1 ON used_id.def_uid1 = d1.id
LEFT JOIN definition_list AS d2 ON ...
LEFT JOIN definition_list AS d3 ON ...

answered Jun 1, 2015 at 14:52

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AdamMc331 · Accepted Answer · 2015-06-01 14:57:11Z

2

You have two options. One option is to join your table several times, once for each column you need to grab, or you can write subqueries inside your select clause for each column, like this:

SELECT
  u.id,
  (SELECT name FROM definition_list WHERE id = u.def_uid1) AS def_uid1,
  (SELECT name FROM definition_list WHERE id = u.def_uid2) AS def_uid2,
  (SELECT name FROM definition_list WHERE id = u.def_uid3) AS def_uid3
FROM used_id u;

Here is an SQL Fiddle example, using both options that I mentioned.

answered Jun 1, 2015 at 14:57

AdamMc331

16.7k11 gold badges78 silver badges134 bronze badges

1 Comment

AdamMc331 Over a year ago

@Strawberry what is your alternative?

Collectives™ on Stack Overflow

mysql select multiple columns of one row from another table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related