Not getting the expected result with 'IN' and subquery

Question

I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the result of id 1 i didn't get the result of id 3.

SELECT count( id ) AS totalDestination, group_concat( ring_to_number ) AS phoneNumbers
    FROM tbl_destinations
    WHERE id
    IN (

    SELECT destinations
    FROM tbl_rings_to_groups
    WHERE id = '1'
    AND user_id = '1'
    )

Can anyone please tell me why i didn't get the destinations=>1,3 in SubQuery?

tbl_rings_to_groups:

enter image description here

tbl_destinations:

enter image description here

More details needed, add a few sample records for tbl_rings_to_groups and tbl_destinations. — , May 01 '13 at 10:49
"why i didn't get the destinations=>1,3 " - in fact, you did. But there is certainly no destination with an id of '1,3' in your database. You should fix your schema, not that query. — Mat, May 01 '13 at 11:01
@Mat in tbl_rings_to_groups there is a field named destinations has the comma separated ID's of table tbl_destinations. And in above subquery return only id 1 not like 1,3 — jogesh_pi, May 01 '13 at 11:03
Yes, and that's exactly your problem. That column should have the same datatype as that of tlb_destinations.id, and not contain strings of any sort. — Mat, May 01 '13 at 11:05
@Mat is there some alternative solution or i have to change the datatype? — jogesh_pi, May 01 '13 at 11:09

score 3 · Accepted Answer · answered May 01 '13 at 11:18

3

You don't get results because the data type of the two tables' id are different and by that, cannot be compared (as 1 != '1'). Try this instead:


DECLARE @destlist varchar(max)
SET @destlist = SELECT destinations FROM tbl_rings_to_groups WHERE id = '1' AND user_id = '1'

SELECT count( id ) AS totalDestination, group_concat( ring_to_number ) AS phoneNumbers
FROM tbl_destinations
WHERE id IN (CONCAT('SELECT ', REPLACE(@destlist), ',', ' UNION ALL SELECT '))

answered May 01 '13 at 11:18

oups, it's t-sql. but you get the idea, right? :) – May 01 '13 at 11:22
perfect, thank you for the accept. Please bear in mind that the code above is sensitive to SQL Injection! so if you plan to make the call accessible "from the outside", or the variable's value may come from outside you need to write a small function to test the string before execute the call. – May 01 '13 at 11:59

score 2 · Answer 2 · answered May 01 '13 at 11:21

The subquery...

SELECT destinations
    FROM tbl_rings_to_groups
    WHERE id = '1'
    AND user_id = '1'

...returns a string containing "1, 3".

Then, you compare this substring to the field "id", which I assume is of type integer. Maybe mysql does some kind of implicit conversion, so the "1" that you see is just the result of converting the string (varchar) "1, 3" to integer.

The right answer is on the comments: You must change your schema to take into account the multiple cardinality of destinations.

score 2 · Answer 3 · edited May 23 '17 at 11:33

First, fix your schema. As soon as possible. There is absolutely no reason to be storing comma separated lists in a table column. Quite the opposite, there a lot of reasons not to. See these answers for detailed explanation:

Is using multiple foreign keys separated by commas wrong, and if so, why?
(by @Simon Righarts)
Is storing a delimited list in a database column really that bad?
(by @Bill Karwin)

In the mean time, you can use this query, that uses the FIND_IN_SET() function:

SELECT count(*) AS totalDestination, 
       group_concat(d.ring_to_number) AS phoneNumbers
FROM tbl_destinations AS d 
  JOIN tbl_rings_to_groups AS rg
    ON FIND_IN_SET( d.id, rg.destinations ) > 0
WHERE rg.id = 1
  AND rg.user_id = 1 ;

But fixing / normalizing your schema should be your first priority.

Not getting the expected result with 'IN' and subquery

3 Answers3