Query for select using where in

Asked Jan 08 '15 at 08:35

Active Jan 08 '15 at 08:42

Viewed 40 times

I am writing a query like

SELECT t.tag
FROM   Tags t,Device_Tags dt
WHERE  t.tag IN (SELECT tag_id
                 FROM   Device_Tags
                 WHERE  device_id = '464')
       AND dt.device_id = '464'

i need it to produce a query like

SELECT t.tag
FROM   Tags t,Device_Tags dt
WHERE  t.tag IN ( 124, 124, 125 )
       AND dt.device_id = '464'

my tables are like

Tags table

tag_id tag
============
123    xyz
124    abc
125    Tags 

Device_tags

id    tag_id        device_id
==============================
1    123,124            1
2    123,124,125        1
3    123,124,125        1

i need all the tag name based on the device_id

Please help me how can i achieve this.

edited Jan 08 '15 at 08:42

Deep

3,162
1
12
21

asked Jan 08 '15 at 08:35

Saikumar Anireddy

1

The table structure for Device_tags is not that much good. You should have different row for each tag_id. – Charvee Shah Jan 08 '15 at 08:37
1

Please, format your post, my eyes are bleeding in both editor and review mode. – potashin Jan 08 '15 at 08:38
1

Do you really store comma separated values in the Device_tags.tag_id column? That's generally a bad idea, will probably only cause you trouble. – jarlh Jan 08 '15 at 08:38
@CharveeShah ,@jarlh i tried inserting single row for each id. but update will be problem in my case. so i used single row for device_id – Saikumar Anireddy Jan 08 '15 at 08:42
`SELECT DISTINCT tag FROM Tags t JOIN Device_Tags dt ON FIND_IN_SET(t.tag_id, dt.tag_id) WHERE dt.device_id = '464';` – shmosel Jan 08 '15 at 08:47
@shmosel Worked it like champ. Thanks a lot, saved my days – Saikumar Anireddy Jan 08 '15 at 09:03

Query for select using where in

0 Answers0