Mysql – Not getting the expected result with ‘IN’ and subquery

MySQLsubquery

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

Best Answer

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

Related Solutions

Mysql – Query performance with subquery and IN clause

Refactor the query as follows:

SELECT
    readings.*
FROM
    (
        SELECT boxsn FROM readings
        WHERE (time >= 1325404800) 
        AND (time < 1326317400) 
        ORDER BY `time` ASC
    ) readings_keys
    LEFT JOIN
    (
        SELECT id AS boxsn FROM boards WHERE siteId = '1'
    ) boards
    USING (boxsn)
    LEFT JOIN readings
    USING (boxsn)
;

Make sure you have the following indexes:

ALTER TABLE boards ADD INDEX siteId_id_ndx (siteId,id);
ALTER TABLE readings ADD INDEX time_boxsn_ndx (time,boxsn);

You can drop the other index

ALTER TABLE readings DROP INDEX boxsn_time_ndx;

You should definitely see a dramatic improvement in performance as the tables grow.

In your case,

The first EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a list of value in memory
The second EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a table.

UPDATE 2012-01-12 14:03 EDT

I refactored it again to make sure the readings keys and boards keys are combined correctly before retrieving the data from the readings table:

SELECT 
    readings.* 
FROM 
    ( 
        SELECT A.* FROM
        (
            SELECT boxsn FROM readings 
            WHERE (time >= 1325404800)  
            AND (time < 1326317400)  
            ORDER BY `time` ASC
        ) A
        LEFT JOIN
        (
            SELECT id AS boxsn
            FROM boards
            WHERE siteId = '1'
        ) B
        USING (boxsn)
        WHERE B.boxsn IS NOT NULL
    ) readings_keys 
    LEFT JOIN readings 
    USING (boxsn) 
;

Mysql – HOW to Show Exam Wise Marks by transposing Columns as Rows in Mysql Report

You can do it like this

SET @sql = NULL;

SELECT GROUP_CONCAT(
         CONCAT('MAX(CASE WHEN examid = ''', exid,
         ''' THEN marks END) `', exnm, '`'))
  INTO @sql
  FROM tbl_masterexam
 WHERE cid = 13;

SET @sql = CONCAT('SELECT s.subject, ', @sql, 
                  '  FROM tbl_Cmarks m JOIN Tbl_subjects s 
                       ON m.subid = s.id 
                    WHERE m.rollno = 1
                      AND m.cid = 13
                    GROUP BY m.subid, s.subject');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

|           SUBJECT | UNIT TEST-1 | UNIT TEST-2 | QUARTERLY EXAMS | UNIT TEST-3 | HALF YEARLY EXAMS | UNIT TEST-4 | PRE - PUBLIC EXAMS |
|-------------------|-------------|-------------|-----------------|-------------|-------------------|-------------|--------------------|
|             తెలుగు |          18 |          20 |              75 |          19 |                45 |          19 |             (null) |
|               हिन्दी |          20 |          19 |              89 |      (null) |                75 |          18 |             (null) |
|           ENGLISH |          21 |          20 |              41 |          16 |                41 |          19 |             (null) |
|       MATHEMATICS |          23 |          22 |              80 |          20 |                45 |          20 |             (null) |
| PHYSICAL SCIENCES |          11 |          10 |              45 |          10 |                40 |          12 |             (null) |
|           BIOLOGY |          11 |          10 |              42 |          10 |                42 |          11 |             (null) |
|    SOCIAL STUDIES |          22 |          20 |              89 |          20 |                40 |          15 |             (null) |

Here is SQLFiddle demo

Best Answer

Related Solutions

Mysql – Query performance with subquery and IN clause

UPDATE 2012-01-12 14:03 EDT

Mysql – HOW to Show Exam Wise Marks by transposing Columns as Rows in Mysql Report

Related Question