CODEWITHSUNDEEP
mysqlselect
Sapnix
Sapnix

Reputation: 55

mysql select multiple columns of one row from another table

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   |

Upvotes: 1

Views: 4878

Answers (2)

AdamMc331
AdamMc331

Reputation: 16710

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.

Upvotes: 2

Marc B
Marc B

Reputation: 360762

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 ...

Upvotes: 3

Related Questions