Query logic, and's and or's

• TheGreenShepherd

  SSCertifiable

  Points: 6979

  More actions

  October 4, 2012 at 2:55 pm

  #282694

  Ok, I feel like I'm just missing something stupid here...I have the following table:

  CustomerID Product

  1 Shoes

  1 Pants

  2 Socks

  2 Pants

  3 Shoes

  3 Socks

  4 Shoes

  4 Pants

  4 Socks

  5 Pants

  5 Shoes

  I need a single query that shoes me everyone who has either pants OR both socks and shoes. Now, to take it a step further, let's say the table actually looks like this:

  CustomerID Product Color

  1 Shoes Black

  1 Pants Black

  2 Socks Blue

  2 Pants Brown

  3 Shoes Blue

  3 Socks Black

  4 Shoes Black

  4 Pants Brown

  4 Socks Black

  5 Pants Blue

  5 Shoes Brown

  How do I write a query that gives me everyone that has either black pants OR both blue sock and brown pants?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 4, 2012 at 3:05 pm

  #1545825

  oops; duplicate post after editing, so i trimmed the oldest. see below.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 4, 2012 at 3:07 pm

  #1545826

  here's one way, by joining the data on itself;

  because all your sample data has either pants or shoes and socks, it's misleading, so I added a couple of dumb examples so you could see stuff gets excluded. note 6,7,8 don't appear in hte results.

  With MyCTE (CustomerID,Product,Color)

  AS

  (

  SELECT '1','Shoes','Black' UNION ALL

  SELECT '1','Pants','Black' UNION ALL

  SELECT '2','Socks','Blue' UNION ALL

  SELECT '2','Pants','Brown' UNION ALL

  SELECT '3','Shoes','Blue' UNION ALL

  SELECT '3','Socks','Black' UNION ALL

  SELECT '4','Shoes','Black' UNION ALL

  SELECT '4','Pants','Brown' UNION ALL

  SELECT '4','Socks','Black' UNION ALL

  SELECT '5','Pants','Blue' UNION ALL

  SELECT '5','Shoes','Brown' UNION ALL

  SELECT '6','Shorts','Blue' UNION ALL

  SELECT '7','Sandals','Blue' UNION ALL

  SELECT '8','Socks','Blue'

  )

  SELECT * FROM MYCTE T1

  LEFT OUTER JOIN MYCTE T2

  ON T1.CustomerID=T2.CustomerID

  WHERE (T1.Product = 'Pants' AND Color='Black')

  OR (T1.Product = 'Shoes'

  AND T2.Product = 'Socks' AND T2.Color = 'Blue'

  )

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• TheGreenShepherd

  SSCertifiable

  Points: 6979

  More actions

  October 4, 2012 at 3:14 pm

  #1545831

  Thanks. Totally worked for my application.