CODEWITHSUNDEEP
mysqljoindenormalized
user2992672
user2992672

Reputation: 408

How to reuse values from multiple columns in the select statement of mysql

I have two tables. Table_1 has three foreign keys that are coming from the table_2. I need to select all rows from table_2 where ids are equal to the values of val_1, val_2 and val_3 of a specific row from table_1. For example:

SELECT val_1, val_2, val_3 from table_1 WHERE id = 1;

And then using the result of the first query run

SELECT name FROM table_2 WHERE id IN (101, 102, 103);

Is there a way to do it in one query?

table_1

id name val_1 val_2 val_3
1 item1 101 102 103
2 item2 104 105 106

table_2

id name
101 sub_item1
102 sub_item2
103 sub_item3
104 sub_item4
105 sub_item5
106 sub_item6

Upvotes: 0

Views: 263

Answers (2)

O. Jones
O. Jones

Reputation: 108676

This is a fairly complex JOIN operation.

SELECT t1.name, 
       t1.val_1, v1.name AS name1,
       t1.val_2, v2.name AS name2,
       t1.val_3, v3.name AS name3
  FROM table_1 t1
  LEFT JOIN table_2 v1 ON t1.val_1 = v1.id
  LEFT JOIN table_2 v2 ON t1.val_2 = v2.id
  LEFT JOIN table_2 v3 ON t1.val_3 = v3.id

And adding to @forpas demo...

But think how much easier your life would be if your table_1 had this normalized layout, with one item per row rather than three.

id  name    val 
1   item1   101 
2   item1   102
3   item1   103
4   item2   104 
5   item2   105
6   item2   106

Then your query would be

SELECT t1.name, t1.val, t2.name AS subname
  FROM table_1 t1
  LEFT JOIN table_2 t2 ON t1.val = t2.id
 WHERE t2.id IN (101, 102, 103) 
 ORDER BY t1.name, t2.name

Fiddle here.

Upvotes: 0

forpas
forpas

Reputation: 164099

One way to do it is to concatenate val_1, val_2 and val_3 in a subquery and use FIND_IN_SET():

SELECT name 
FROM table_2 
WHERE FIND_IN_SET(
        id, 
        (SELECT CONCAT_WS(',', val_1, val_2, val_3) FROM table_1 WHERE id = 1)
      );

Or, join the tables:

SELECT t2.name 
FROM table_2 t2 INNER JOIN table_1 t1
ON t2.id IN (t1.val_1, t1.val_2, t1.val_3)
WHERE t1.id = 1;

See the demo.

Upvotes: 2

Related Questions