I am very new to SQL and not a programmer by trade. I know enough to be dangerous. I need a query (I'm using Toad for SQL) that extracts information when multiple conditions from the same table are met ... essentially pull data when it equals this AND equals this. Example:

Last First Order# Item
Doe John 1111 Candy
Doe John 1111 Gum
Doe John 1111 Chips
Doe John 2222 Candy
Doe John 2222 Chips
Smith Jane 3333 Candy
Smith Jane 4444 Candy
Smith Jane 4444 Chips
Smith Jane 5555 Gum

Right now, my select statement is "Select Last, First, Order # when Item in ('Candy', 'Gum')". I'm getting six returns since it is matching entries with either "Candy" or either "Gum". However, I need a statement that pulls a value when both 'Candy' and 'Gum' are purchased by the same customer in the same order #. Given the data above, I only want "Doe, John, 1111" returned. Can anyone help? And just for the record, this is not a homework assignment ...

you could try this syntax to pull your data,
SELECT DISTINCT Last, First, Order# FROM your table WHERE Item = 'Candy' OR 'Gum';

Here is the SQL statement you should use. Enjoy.

Select t1.Last, t1.First, t1.Order from table t1, table t2 where t2.Order = t1.Order and t1.Item = 'Candy' and t2.Item = 'Gum';
Web Directory