CODEWITHSUNDEEP
mysqlsql
carol1287
carol1287

Reputation: 395

MySql Filter Group Query

I have a group query that give me the total count of records available on a LEFT JOIN table. That works fine, however I am trying to add now a filter condition to only show a precise record and the way I am doing it is not working. Does anyone see what I am doing wrong? Thanks so much. Below is the code I have.

table1
--------

    t1.abc_uniqueid
    t1.abc_serial
    t1.abc_gender
    t1.abc_fullname
    t1.abc_phone
    t1.abc_bcu_uniqueid

2 Record Sample Data:

    1
    12344
    'male'
    'john doe'
    '888888888'
    7777

    2
    12345
    'female'
    'jane doe'
    '888888889'
    7777


table2
-------

    lyy_uniqueid
    lyy_abc_uniqueid
    ...

2 Record Sample Data:

    10
    1
    ...

    11
    2
    ...

My GROUP query that works:

SELECT
    t1.abc_uniqueid,
    t1.abc_serial As serial,
    t1.abc_gender As gender,
    t1.abc_fullname As fullname,
    t1.abc_phone As phone,
    IFNULL(v.TotalNo, 0) TotalNo   
    FROM 
    table1 t1   LEFT JOIN   ( 
    SELECT lyy_abc_uniqueid, COUNT(*) TotalNo
    FROM table2 v
    GROUP BY lyy_abc_uniqueid   ) v 
    ON v.lyy_abc_uniqueid = t1.abc_uniqueid   
    AND t1.abc_bcu_uniqueid=7777;

I want to add a new condition like this: 

    AND t1.abc_serial=12345;

I am able to get the correct result if I use AND t1.abc_bcu_uniqueid=7777 but as soon as I add the new condition, the results are all wrong. For example, if table1 has a row with t1.abc_serial = 1, I want to get the group left join only for that.

Upvotes: 0

Views: 66

Answers (2)

Bhanuchander Udhayakumar
Bhanuchander Udhayakumar

Reputation: 1646

Hope this is what you want:

SELECT
    t1.abc_uniqueid,
    t1.abc_serial As serial,
    t1.abc_gender As gender,
    t1.abc_fullname As fullname,
    t1.abc_phone As phone,
    IFNULL(v.TotalNo, 0) TotalNo   
    FROM 
    table1 t1   LEFT JOIN   ( 
    SELECT lyy_abc_uniqueid, COUNT(*) TotalNo
    FROM table2 v
    GROUP BY lyy_abc_uniqueid   ) v 
    ON (v.lyy_abc_uniqueid = t1.abc_uniqueid)   
    where t1.abc_bcu_uniqueid=7777 and t1.abc_serial=12345;

Upvotes: 1

Raspberryano
Raspberryano

Reputation: 159

Maybe it is because you must add field abc_serial to GROUP BY

Upvotes: 0

Related Questions