Need help with SQL query

Mon, 06 Jun 2011 12:22:39 -0700

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 ...

Re: Need help with SQL query

Fri, 29 Jul 2011 00:49:49 -0700

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

Re: Need help with SQL query

Thu, 08 Mar 2012 14:24:27 -0700

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

'Need help with SQL query' Thread RSS Feed

Need help with SQL query

Re: Need help with SQL query

Re: Need help with SQL query