help with writing subquery

• rash3554

  SSCommitted

  Points: 1984

  More actions

  October 21, 2015 at 6:43 am

  #321707

  Hi there,

  I need a little help writing the query

  Create table test(CompanyID int, VendorID varchar(10),SalesinMilUSD numeric(10,4));

  insert into test(1,1000,10.3);

  insert into test(1,2000,99.3);

  insert into test(2,2000,0.8);

  insert into test(3,3000,12.9);

  insert into test(3,2000,13.1);

  insert into test(3,1000,12.9);

  insert into test(4,2000,5.24);

  insert into test(4,5000,6.1);

  ID is Company ID. We get Sales of Company from different Vendors.eg: CompanyID=1 is Microsoft and SalesinMilUSD=10.3 provided by vendor 1000 and by Vendor 2000 it is 99.3 MillionUSD.

  I want to get all Companies that get data from vendorid=2000 as one of the Vendor along with others i.e. CompanyID=1,3,4. CompanyID=2 has value from only one Vendor so I should not print this.

  How can I do this?

  Thanks

  R

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 21, 2015 at 6:49 am

  #1834616

  Not quite sure I follow. What should be the output from the sample data listed?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 21, 2015 at 6:53 am

  #1834621

  rash3554 (10/21/2015)

  ---

  Hi there,

  I need a little help writing the query

  Create table test(CompanyID int, VendorID varchar(10),SalesinMilUSD numeric(10,4));

  insert into test(1,1000,10.3);

  insert into test(1,2000,99.3);

  insert into test(2,2000,0.8);

  insert into test(3,3000,12.9);

  insert into test(3,2000,13.1);

  insert into test(3,1000,12.9);

  insert into test(4,2000,5.24);

  insert into test(4,5000,6.1);

  ID is Company ID. We get Sales of Company from different Vendors.eg: CompanyID=1 is Microsoft and SalesinMilUSD=10.3 provided by vendor 1000 and by Vendor 2000 it is 99.3 MillionUSD.

  I want to get all Companies that get data from vendorid=2000 as one of the Vendor along with others i.e. CompanyID=1,3,4. CompanyID=2 has value from only one Vendor so I should not print this.

  How can I do this?

  Thanks

  R

  SELECT CompanyID, VendorID, SalesinMilUSD

  FROM (

  SELECT t1.CompanyID, t1.VendorID, t1.SalesinMilUSD,

  VendorCount = COUNT(*) OVER(PARTITION BY CompanyID)

  FROM #test t1

  WHERE EXISTS (SELECT 1 FROM #test t2 WHERE t2.CompanyID = t1.CompanyID AND t2.VendorID = 2000)

  ) d

  WHERE VendorCount > 1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• rash3554

  SSCommitted

  Points: 1984

  More actions

  October 21, 2015 at 8:39 am

  #1834700

  Thanks for helping me.. I had to add more conditions on top of this but this foundation really helped me