Beginner SQL Subquery Help

• owninginc2k11

  Valued Member

  Points: 53

  More actions

  October 7, 2016 at 4:44 pm

  #330739

  Hello I am currently learning SQL in school and I have been having a lot of trouble trying to figure out how to create a sub query if anyone could help it would help me out greatly, thanks

  This is what I need to do,

  Query the Purchasing.PurchaseOrderHeader Table to find all PurchaseOrderId's that have the ShipMethods named ("OVERSEAS - DELUXE" or "OVERNIGHT J-FAST"). Use a subquery of the Purchasing.ShipMethod

  I have tried to do this about 40 different ways and I still can't figure it out, here are 2 different ways I tried to accomplish this,

  USE AdventureWorks2012;

  GO

  --First method

  Select ShipMethodID, Name

  FROM Purchasing.ShipMethod

  WHERE Name IN (SELECT NAME = 'OVERSEAS - DELUXE'

  OR 'OVERNIGHT J-FAST'

  FROM Purchasing.PurchaseOrderHeader);

  --Second Method

  SELECT ShipMethodID, Name

  FROM Purchasing.ShipMethod

  WHERE Id IN (SELECT Name

  FROM Purchasing.PurchaseOrderHeader

  WHERE Name = 'OVERSEAS - DELUXE');

  And of course neither is right, this has had me stuck for several days so if anyone can help thank you very much.

  -Tony

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 7, 2016 at 6:11 pm

  #1905488

  owninginc2k11 (10/7/2016)

  ---

  Hello I am currently learning SQL in school and I have been having a lot of trouble trying to figure out how to create a sub query if anyone could help it would help me out greatly, thanks

  This is what I need to do,

  Query the Purchasing.PurchaseOrderHeader Table to find all PurchaseOrderId's that have the ShipMethods named ("OVERSEAS - DELUXE" or "OVERNIGHT J-FAST"). Use a subquery of the Purchasing.ShipMethod

  I have tried to do this about 40 different ways and I still can't figure it out, here are 2 different ways I tried to accomplish this,

  USE AdventureWorks2012;

  GO

  --First method

  Select ShipMethodID, Name

  FROM Purchasing.ShipMethod

  WHERE Name IN (SELECT NAME = 'OVERSEAS - DELUXE'

  OR 'OVERNIGHT J-FAST'

  FROM Purchasing.PurchaseOrderHeader);

  --Second Method

  SELECT ShipMethodID, Name

  FROM Purchasing.ShipMethod

  WHERE Id IN (SELECT Name

  FROM Purchasing.PurchaseOrderHeader

  WHERE Name = 'OVERSEAS - DELUXE');

  And of course neither is right, this has had me stuck for several days so if anyone can help thank you very much.

  -Tony

  You have it backwards:

  Try this:

  select *

  from [Purchasing].[PurchaseOrderHeader]

  where ShipMethodID in (select ShipMethodID from [Purchasing].[ShipMethod] where Name in ('OVERSEAS - DELUXE','OVERNIGHT J-FAST'));

  Any questions?

• owninginc2k11

  Valued Member

  Points: 53

  More actions

  October 8, 2016 at 4:01 pm

  #1905540

  Thank you so much

  -Tony