Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
More details needed, add a few sample records for tbl_rings_to_groups and tbl_destinations. –  Mark 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 Answers 3

up vote 3 down vote accepted

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 '))
share|improve this answer
    
oups, it's t-sql. but you get the idea, right? :) –  Mark 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. –  Mark May 1 '13 at 11:59

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.

share|improve this answer

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.

share|improve this answer
    
thanks again for the explanation :) –  jogesh_pi May 2 '13 at 3:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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