How to use in clause in subquery

Question

I have two tables like follows,

Table1:

ID      NAME    
------  --------
IM-1    Pencil  
IM-2    Pen     
IM-3    NoteBook
IM-4    Eraser

TABLE-2:

ID      ITEM_ID       
------  --------------
MT-1    IM-1          
MT-2    IM-1,IM-2,IM-3     
MT-3    IM-1,IM-2,IM-4

Required Result is :

ID      ITEMNAME
------  --------
MT-1    Pencil  
MT-2    Pencil,Pen,NoteBook  
MT-3    Pencil,Pen,Eraser

But by using the query

SELECT T2.ID, 
          (SELECT T1.NAME 
           FROM TABLE1 AS T1 
           WHERE T1.ID IN (T2.ITEM_ID)) AS ITEMNAME 
    FROM TABLE2 AS T2

I get the result:

ID      ITEMNAME
------  --------
MT-1    Pencil  
MT-2    (NULL)  
MT-3    (NULL)

Can anyone help me pls?

It's not good to store comma separated values in database columns. — ypercubeᵀᴹ, Jan 31 '12 at 15:32
@knittl. The OP needs to do the reverse of `GROUP_CONCAT` on `ITEM_ID` before it can be joined to Table1. — Conrad Frix, Jan 31 '12 at 15:33
[Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad) **Yes, it's really that bad.** — ypercubeᵀᴹ, Jan 31 '12 at 15:33

score 1 · Answer 1 · edited May 23 '17 at 12:20

1

Without normalizing you can try this. But this query won't be SARGable, and as ypercube pointed out storing a comma separated list in a db is a bad idea.

  SELECT T2.ID,
         GROUP_CONCAT(T1.NAME SEPARATOR  ',')
   FROM TABLE2 AS T2
        INNER JOIN  TABLE2 AS T1
        ON FIND_IN_SET (T1.ID , T2.ITEM_ID )
   GROUP BY 
         T2.ID

edited May 23 '17 at 12:20

Community

1
1

answered Jan 31 '12 at 15:42

Conrad Frix

51,984
12
96
155

Thank u for ur reply Conrad Frix. got my required result. – Sangeetha Krishnan Jan 31 '12 at 15:52

MRM · Answer 2 · 2012-01-31T15:45:44.957

0

SELECT T2.ID, T1.NAME as ITEMNAME 
FROM TABLE2 AS T2 LEFT JOIN TABLE1 AS T1 ON 
     FIND_IN_SET(T1.ID,T2.ITEM_ID)>0 AND FIND_IN_SET(T1.ID,T2.ITEM_ID) IS NOT null;

edited Jan 31 '12 at 15:45

answered Jan 31 '12 at 15:34

MRM

435
2
10

Thank u for reply ur MRM. got my required result. – Sangeetha Krishnan Jan 31 '12 at 15:50

score 0 · Accepted Answer · answered Jan 31 '12 at 15:44

0

try this:

SELECT   b.ID, 
         GROUP_CONCAT(a.Name) as ItemName
FROM     Table2 b INNER JOIN Table1 a
              ON FIND_IN_SET(a.name, b.Item_ID)
GROUP BY b.ID

answered Jan 31 '12 at 15:44

John Woo

258,903
69
498
492

Thank u for ur reply johntotetwoo. got my required result. – Sangeetha Krishnan Jan 31 '12 at 15:51

score 0 · Answer 4 · answered Jan 31 '12 at 15:49

0

what you are looking for is a recursive way to fetch the names of the items from a list of items.

it need to be done using a scripting language in a program.. sql doesn't support such way of fetching data..

you need to load the list of values in a string then split them then replace them with their values in the database..

answered Jan 31 '12 at 15:49

Fahd Hamad Al-Abdulqader

71
2

Thanks Fahd Hamad Al-Abdulqad. But i can get my result by this query SELECT T2.ID, GROUP_CONCAT(T1.NAME SEPARATOR ',') FROM TABLE2 AS T2 INNER JOIN TABLE1 AS T1 ON FIND_IN_SET (T1.ID , T2.ITEM_ID ) GROUP BY T2.ID – Sangeetha Krishnan Feb 02 '12 at 06:58

How to use in clause in subquery

4 Answers4

Linked