0

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. – user23243 May 1 '13 at 10:49
  • @Mark i have updated my question please take a look.. – jogesh_pi May 1 '13 at 10:53
  • 1
    "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 1 '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 1 '13 at 11:03
  • 1
    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 1 '13 at 11:05
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 '))
  • oups, it's t-sql. but you get the idea, right? :) – user23243 May 1 '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. – user23243 May 1 '13 at 11:59
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.

2

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.