smehsoud smehsoud - 3 months ago 9
MySQL Question

how to get data NOT IN next tabel mysql

I have mysql query that return all data

SELECT state,`County`,`CountyFIPS`,count(`email_address`) as email
FROM `campaign_emails`
Where state='AK'
AND CountyFIPS NOT IN
(select group_concat(`county_fipscode` separator ',')
from order_cart where flyer_id='1' AND user_id='400'
)
GROUP BY `CountyFIPS`


But the same query when I manually pass it returns my exact value

SELECT state,`County`,`CountyFIPS`,count(`email_address`) as email
FROM `campaign_emails`
Where state='AK'
AND CountyFIPS NOT IN (02261,02220,02180,02170,02240,02020,02090,02110)
GROUP BY `CountyFIPS`


please help anybody, what is the reason?

Answer

IN accepts the results of a SELECT query but a string with comma-separated values as a hard-coded string is seen as 1 value only, so everything will match in your first query.

Mysql sees your condition in the first query as:

... AND CountyFIPS NOT IN '1,2,3,4,5'    // this is not the array of values you want to use

So you can simplify your code to:

SELECT state,County,CountyFIPS,count(email_address) as email
FROM campaign_emails Where state='AK' 
    AND CountyFIPS NOT IN (select county_fipscode from order_cart where flyer_id='1' AND user_id='400') GROUP BY CountyFIPS
                                  ^^^^^^^^^^^^^^^ here