Using IN() and NOT IN() with your SQL statement

[admin]

Say you have two related tables:


Customer table
ID - autoincrement / identity
Name - text / varchar
City - text / varchar

Purchases table
CustID - integer / integer
Item - text / varchar

Now you want to grab the Name field from the Customer table and the Item from the Purchases table. However, you only want those customers that purchased a Gizmo and a Gadget.

SQL = "Select Name, Item From Customer, Purchases Where Item In("Gizmos", "Gadgets")"

Now suppose you only wanted the customers that purchased anything except Gizmos and Gadgets.

SQL = "Select Name, Item From Customer, Purchases Where Item Not In("Gizmos", "Gadgets")"