sql "NOT" in query

hello EE
i face an interesting task!
i have a list of countries:
$cntry_list='United Kingdom,Austria,Belarus,Belgium,Bulgaria,Croatia,Czech Republic,......";

i need to select from my DB customers which countries are not in the list ($cntry_list)
i know how to select customers which countries are in the list:

$q = " SELECT * FROM customers_db WHERE customer_id IN ($cntry_list)";
but how to select customers that are not in the list?

LVL 4
nzrubinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Just add the NOT keyword.
$q = " SELECT * FROM customers_db WHERE customer_id NOT IN ($cntry_list)";

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
If this is MySQL, then you can do <> ANY also:
http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

$q = " SELECT * FROM customers_db WHERE customer_id <> ANY ($cntry_list)";

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Sorry, I forgot the ANY keyword is only for subqueries.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

nzrubinAuthor Commented:
actually i need realize this kind of query where country is no ant integer

$qCtry = "SELECT * FROM customers_db WHERE country NOT IN ($cntry_list)";


here customer_id is INTEGER therefore it would work
$q = " SELECT * FROM customers_db WHERE customer_id NOT IN ($cntry_list)";

but cant i select NOT IN if country is not INTEGER it is just a varchar
0
Kevin CrossChief Technology OfficerCommented:
Sorry, I wasn't paying attention to the column name.  I figured if you posted it is the correct column. ;)

$qCtry = "SELECT * FROM customers_db WHERE country NOT IN ($cntry_list)";

That query should work fine unless something is not coming back the way you expect.  Post back if so.
0
Kevin CrossChief Technology OfficerCommented:
Just make sure you list in PHP is specified with the proper quoting.

$cntry_list="'United Kingdom','Austria','Belarus','Belgium','Bulgaria','Croatia','Czech Republic'";

This is so the SQL after variable replacement will be correct as string literals need single quotes.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kerwinsiyCommented:
hi nzrubin, could you give us your table struct for customer and country.
0
Roger BaklundCommented:
You are testing the country list agains the customer_id? That won't work. Do you have a column in the table named 'country' or similar? That is the column you must use in the WHERE clause.

Also, each country name must be put in quotes:
$cntry_list = 'United Kingdom,Austria,Belarus,Belgium,Bulgaria,Croatia,Czech Republic';
$cntry_list = explode(',',$cntry_list); # make an array
$cntry_list = "'".implode("','",$cntry_list)."'";  # make quotes string
$q = " SELECT * FROM customers_db WHERE country NOT IN ($cntry_list)";

Open in new window

0
jamesguCommented:
you may have to  change the list to

$cntry_list="'United Kingdom','Austria','Belarus','Belgium','Bulgaria','Croatia','Czech Republic',......";


0
kerwinsiyCommented:

SELECT * FROM customers_db WHERE CAST(customer_id AS VARCHAR(30))  NOT IN ($cntry_list)
0
BrandonGalderisiCommented:
What DB engine?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.