select query

• znkin

  Old Hand

  Points: 382

  More actions

  September 16, 2012 at 12:36 pm

  #267774

  I am using SQL 2008 and I need help to write a select query for the following output

  I need list of distinct CustId who have ordered both ProdId 1 and 4. Also if the custid has ordered ProdId 0 then that custid should not be selected

  table schema and data are given below and as per the data given the select query should display data cust1 and cust5 as both of them have ProdId 1 and 4 and also neither of them have ProdId 0.

  create table product_details

  (

  TempId int identity,

  CustId varchar(50),

  ProdId char(1),

  enteredon datetime

  )

  insert into product_details values('cust1',1,getdate())

  insert into product_details values('cust1',2,getdate())

  insert into product_details values('cust1',3,getdate())

  insert into product_details values('cust1',4,getdate())

  insert into product_details values('cust2',1,getdate())

  insert into product_details values('cust2',2,getdate())

  insert into product_details values('cust2',3,getdate())

  insert into product_details values('cust3',0,getdate())

  insert into product_details values('cust3',1,getdate())

  insert into product_details values('cust3',2,getdate())

  insert into product_details values('cust4',1,getdate())

  insert into product_details values('cust4',2,getdate())

  insert into product_details values('cust4',3,getdate())

  insert into product_details values('cust4',4,getdate())

  insert into product_details values('cust4',0,getdate())

  insert into product_details values('cust5',1,getdate())

  insert into product_details values('cust5',2,getdate())

  insert into product_details values('cust5',3,getdate())

  insert into product_details values('cust5',4,getdate())

  thanks

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 16, 2012 at 12:51 pm

  #1538350

  Hi

  as a start, suggest you read the article below...it seems to be very clsoe to what you are asking and will explain the method far better than I can.

  http://www.sqlservercentral.com/articles/T-SQL/88244/

  if you still have issues, please post back.

  kind regards

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Andy Hyslop

  SSCrazy Eights

  Points: 9198

  More actions

  September 17, 2012 at 2:51 am

  #1538445

  Something like this?

  SELECT DISTINCT

  CustId

  FROM

  (

  SELECT

  CustId

  FROM

  product_details

  WHERE

  ProdId = 1

  AND CustId IN (SELECT DISTINCT CustId FROM product_details WHERE ProdId = 4)

  EXCEPT

  SELECT CustId

  FROM

  product_details

  WHERE

  ProdId = 0

  ) AS A

  Andy

  ==========================================================================================================================
  A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

• Sudhakar Vallamsetty

  SSC-Addicted

  Points: 407

  More actions

  September 17, 2012 at 3:07 am

  #1538449

  Check if the below one helps ur purpose.

  select distinct custid

  from product_details

  where prodid in (1,4) and custid not in (select custid from product_details where prodid=0)

  *******:cool:
  Sudhakar

• rhythmk

  SSCertifiable

  Points: 7271

  More actions

  September 17, 2012 at 3:17 am

  #1538455

  znkin (9/16/2012)

  ---

  I am using SQL 2008 and I need help to write a select query for the following output

  I need list of distinct CustId who have ordered both ProdId 1 and 4. Also if the custid has ordered ProdId 0 then that custid should not be selected

  table schema and data are given below and as per the data given the select query should display data cust1 and cust5 as both of them have ProdId 1 and 4 and also neither of them have ProdId 0.

  create table product_details

  (

  TempId int identity,

  CustId varchar(50),

  ProdId char(1),

  enteredon datetime

  )

  insert into product_details values('cust1',1,getdate())

  insert into product_details values('cust1',2,getdate())

  insert into product_details values('cust1',3,getdate())

  insert into product_details values('cust1',4,getdate())

  insert into product_details values('cust2',1,getdate())

  insert into product_details values('cust2',2,getdate())

  insert into product_details values('cust2',3,getdate())

  insert into product_details values('cust3',0,getdate())

  insert into product_details values('cust3',1,getdate())

  insert into product_details values('cust3',2,getdate())

  insert into product_details values('cust4',1,getdate())

  insert into product_details values('cust4',2,getdate())

  insert into product_details values('cust4',3,getdate())

  insert into product_details values('cust4',4,getdate())

  insert into product_details values('cust4',0,getdate())

  insert into product_details values('cust5',1,getdate())

  insert into product_details values('cust5',2,getdate())

  insert into product_details values('cust5',3,getdate())

  insert into product_details values('cust5',4,getdate())

  thanks

  URL provided by Livingston is best suited for your problem.

  --rhythmk
  ------------------------------------------------------------------
  To post your question use below link
  
  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
  🙂

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 17, 2012 at 3:18 am

  #1538456

  There's lots of ways of doing this, here's another

  SELECT CustID

  FROM product_details

  WHERE ProdId = 1

  INTERSECT

  SELECT CustID

  FROM product_details

  WHERE ProdId = 4

  EXCEPT

  SELECT CustID

  FROM product_details

  WHERE ProdId = 0;

  Also this

  WITH Summary AS (

  SELECT CustID,

  SUM(CASE WHEN ProdId = 0 THEN 1 ELSE 0 END) AS NumProdId0,

  SUM(CASE WHEN ProdId = 1 THEN 1 ELSE 0 END) AS NumProdId1,

  SUM(CASE WHEN ProdId = 4 THEN 1 ELSE 0 END) AS NumProdId4

  FROM product_details

  GROUP BY CustID)

  SELECT CustID

  FROM Summary

  WHERE NumProdId0=0 AND NumProdId1>0 AND NumProdId4>0;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Andy Hyslop

  SSCrazy Eights

  Points: 9198

  More actions

  September 17, 2012 at 3:25 am

  #1538460

  select distinct custid

  from product_details

  where prodid in (1,4) and custid not in (select custid from product_details where prodid=0)

  Nope that wont work the IN works like an OR so CustID 2 is returned in the result set..

  Andy

  ==========================================================================================================================
  A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

• Sudhakar Vallamsetty

  SSC-Addicted

  Points: 407

  More actions

  September 17, 2012 at 3:43 am

  #1538469

  I have overlooked at the problem statement and gave the wrong query.

  Thanks Andy for correcting me.

  *******:cool:
  Sudhakar

• SoCal_DBD

  Hall of Fame

  Points: 3379

  More actions

  September 17, 2012 at 10:54 am

  #1538723

  Another option:

  SELECT pd.CustID

  FROM Product_Details pd

  LEFT JOIN (SELECT CustID FROM Product_Details WHERE ProdID = 0) c ON pd.CustID = c.CustID

  WHERE pd.ProdID IN (1, 4)

  AND c.CustID IS NULL

  GROUP BY pd.CustID

  HAVING COUNT(*) = 2

• ScottPletcher

  SSC Guru

  Points: 101087

  More actions

  September 17, 2012 at 1:10 pm

  #1538790

  SELECT

  CustId

  FROM dbo.product_details

  GROUP BY

  CustId

  HAVING

  MAX(CASE WHEN ProdId = 0 THEN 1 ELSE 0 END) = 0 AND

  MAX(CASE WHEN ProdId = 1 THEN 1 ELSE 0 END) = 1 AND

  MAX(CASE WHEN ProdId = 4 THEN 1 ELSE 0 END) = 1

  ORDER BY

  CustId

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 17, 2012 at 2:04 pm

  #1538808

  I came out with the same code as Jeff showed in the article, but I believe that Scott's code would be better as it is a single table scan instead of 2 or 3.

  And now call me crazy, but execution plans show one thing and time indicators show another on performance issues. Maybe is because I'm using temp tables instead of normal physical tables, but the statement with 3 table scans is performing faster than the other two. And the statement with 1 table scan is the slowest.

  Can anyone confirm or deny this?

  This is the code I used:

  --insert into #product_details

  --SELECT TOP 1000000

  --'CUST' + CAST( (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) / 4) + 6 AS varchar(10)),

  --ABS(CHECKSUM(NEWID())) % 5,

  --GETDATE()

  --FROM sys.all_columns ac1

  -- CROSS JOIN sys.all_columns ac2

  DECLARE @DATE datetime2, @dummy varchar(50)

  SET @DATE = SYSDATETIME()

  SELECT @dummy = CustId

  FROM(

  SELECT CustId

  FROM #product_details

  WHERE ProdId IN( 1,4)

  GROUP BY CustId HAVING COUNT(DISTINCT prodid) = 2

  EXCEPT

  SELECT CustId

  FROM #product_details

  WHERE ProdId = 0) t

  PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

  SET @DATE = SYSDATETIME()

  SELECT @dummy = CustId

  FROM

  (SELECT CustId

  FROM #product_details

  WHERE ProdId = 1

  AND CustId IN (SELECT DISTINCT CustId FROM #product_details WHERE ProdId = 4)

  EXCEPT

  SELECT CustId

  FROM #product_details

  WHERE ProdId = 0) AS A

  PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

  SET @DATE = SYSDATETIME()

  SELECT

  @dummy = CustId

  FROM #product_details

  GROUP BY

  CustId

  HAVING

  MAX(CASE WHEN ProdId = 0 THEN 1 ELSE 0 END) = 0 AND

  MAX(CASE WHEN ProdId = 1 THEN 1 ELSE 0 END) = 1 AND

  MAX(CASE WHEN ProdId = 4 THEN 1 ELSE 0 END) = 1

  ORDER BY

  CustId

  PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

  SET @DATE = SYSDATETIME()

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ScottPletcher

  SSC Guru

  Points: 101087

  More actions

  September 17, 2012 at 2:40 pm

  #1538817

  Luis Cazares (9/17/2012)

  ---

  I came out with the same code as Jeff showed in the article, but I believe that Scott's code would be better as it is a single table scan instead of 2 or 3.

  And now call me crazy, but execution plans show one thing and time indicators show another on performance issues. Maybe is because I'm using temp tables instead of normal physical tables, but the statement with 3 table scans is performing faster than the other two. And the statement with 1 table scan is the slowest.

  Can anyone confirm or deny this?

  This is the code I used:

  --insert into #product_details

  --SELECT TOP 1000000

  --'CUST' + CAST( (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) / 4) + 6 AS varchar(10)),

  --ABS(CHECKSUM(NEWID())) % 5,

  --GETDATE()

  --FROM sys.all_columns ac1

  -- CROSS JOIN sys.all_columns ac2

  DECLARE @DATE datetime2, @dummy varchar(50)

  SET @DATE = SYSDATETIME()

  SELECT @dummy = CustId

  FROM(

  SELECT CustId

  FROM #product_details

  WHERE ProdId IN( 1,4)

  GROUP BY CustId HAVING COUNT(DISTINCT prodid) = 2

  EXCEPT

  SELECT CustId

  FROM #product_details

  WHERE ProdId = 0) t

  PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

  SET @DATE = SYSDATETIME()

  SELECT @dummy = CustId

  FROM

  (SELECT CustId

  FROM #product_details

  WHERE ProdId = 1

  AND CustId IN (SELECT DISTINCT CustId FROM #product_details WHERE ProdId = 4)

  EXCEPT

  SELECT CustId

  FROM #product_details

  WHERE ProdId = 0) AS A

  PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

  SET @DATE = SYSDATETIME()

  SELECT

  @dummy = CustId

  FROM #product_details

  GROUP BY

  CustId

  HAVING

  MAX(CASE WHEN ProdId = 0 THEN 1 ELSE 0 END) = 0 AND

  MAX(CASE WHEN ProdId = 1 THEN 1 ELSE 0 END) = 1 AND

  MAX(CASE WHEN ProdId = 4 THEN 1 ELSE 0 END) = 1

  ORDER BY

  CustId

  PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

  SET @DATE = SYSDATETIME()

  Timing seems that way.

  I would still be leery of additional I/O vs other time, but if you are willing to scan the table 3 times to get response time, then I would say do this:

  SELECT

  CustId

  FROM #product_details pd1

  WHERE

  prodid = 1 AND

  EXISTS(SELECT 1 FROM #product_details pd2 WHERE pd2.custid = pd1.custid AND pd2.prodid = 4) AND

  NOT EXISTS(SELECT 1 FROM #product_details pd3 WHERE pd3.custid = pd1.custid AND pd3.prodid = 0)

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".