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:

tbl_destinations:

Answer 1

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 '))

Answer 2

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.

Answer 3

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.