CODEWITHSUNDEEP
mysqlsql
user3361194
user3361194

Reputation: 1

IN by other column

How to put a column in the IN?

Example:

SELECT tb1.*, 
       (SELECT GROUP_CONCAT(name) 
          FROM tb2 
         WHERE id IN( tb1.ids_tb2 )) AS names
  FROM tb1

Column tb1.ids_tb2 stores "1,2,3"

This is not working.

++++

I need 'ids_tb2' inside the IN

Example Tables http://sqlfiddle.com/#!9/08cfa9/1

Ids_tb2 = 1,3,4 Names != Fruit1,Fruit3,Fruit4

tks

Upvotes: 0

Views: 54

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Your table structure is not really ideal for this task. In database design you should always preserve the 'atomicity' of data, i. e. you should never work with lists of ids in any column. Instead use a link table lnk like this

| gid | fid |
|-----|-----|
|   1 |   1 |
|   1 |   3 |
|   1 |   4 |
|   2 |   1 |
|   2 |   5 |

Having such a structure you can then very easily do

SELECT grp , GROUP_CONCAT(name) fruits 
FROM tb1 
INNER JOIN lnk ON gid=grid
INNER JOIN tb2 ON frid=fid
GROUP BY grp

see here (demo).

The result will be 

|  grp |               fruits |
|------|----------------------|
| Blue | Fruit1,Fruit3,Fruit4 |
|  Red |        Fruit1,Fruit5 |

Having said that - you could also do the following with your original database design (see here):

SELECT tb1.id,`group`,GROUP_CONCAT(name) fruits 
FROM tb1 
INNER JOIN tb2 ON FIND_IN_SET(tb2.id,ids_tb2)>0
GROUP BY `group`

Although I would not recommend doing this. Also you should not be using reserved SQL names for your columns like group, as you will always have to use back-ticks to mask them.

Revisiting ...
Actually, your initial approach does also work, you just have to replace your IN (..) clause by a FIND_IN_SET(..) function call like

SELECT tb1.*, 
  (SELECT GROUP_CONCAT(name) FROM tb2 
   WHERE FIND_IN_SET(tb2.id, tb1.ids_tb2)>0) names
FROM tb1

Upvotes: 1

RohrerF
RohrerF

Reputation: 33

You can do a simple select in the in. Just like this:

SELECT tb1.*, 
   (SELECT GROUP_CONCAT(name) 
      FROM tb2 
     WHERE id IN(select * from tb1.ids_tb2 )) AS names
FROM tb1

Upvotes: 0

Related Questions