?
Solved

sql "NOT" in query

Posted on 2008-11-04
11
Medium Priority
?
196 Views
Last Modified: 2010-03-20
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?

0
Comment
Question by:nzrubin
11 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882528
Just add the NOT keyword.
$q = " SELECT * FROM customers_db WHERE customer_id NOT IN ($cntry_list)";

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882539
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882548
Sorry, I forgot the ANY keyword is only for subqueries.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Author Comment

by:nzrubin
ID: 22882601
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882627
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1200 total points
ID: 22882642
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
 
LVL 2

Expert Comment

by:kerwinsiy
ID: 22882646
hi nzrubin, could you give us your table struct for customer and country.
0
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 200 total points
ID: 22882650
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
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 200 total points
ID: 22882663
you may have to  change the list to

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


0
 
LVL 2

Assisted Solution

by:kerwinsiy
kerwinsiy earned 400 total points
ID: 22882729

SELECT * FROM customers_db WHERE CAST(customer_id AS VARCHAR(30))  NOT IN ($cntry_list)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22882869
What DB engine?
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question