We help IT Professionals succeed at work.

sql 2008  sub query

I HAVE TWO TABLES.  AND ORDER TABLE AND A PRODUCTS TABLE.  THE ORDERS TABLE HAS THE ORDER ID AND THE ShipVia INFORMATION CONTAINED IN IT.  
THE PRODUCTS TABLE HAS THE PRODUCT NAME  AND PRODUCT ID CONTAINED ON THIS TABLE.
I WAS TRYING TO REPLY TO THIS REQUEST:
Write a subquery that shows all of the products that were shipped via the same method as OrderID 10248.
The shippedVia field contains one digit numbers on the Orders table from 1 to 5.
THIS IS THE CODE I TRIED TO CREATE:

SELECT *
FROM Products
WHERE EXIST (SELECT * FROM Orders WHERE ShipVia = 3)
THIS IS THE ERROR MESSAGE I RECEIVED
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

WHERE AM I MAKING MY MISTAKES AND WHAT WOULD BE THE CORRECT SYNTAX?
Comment
Watch Question

Top Expert 2010
Commented:
Assuming productId is relation between the two:

SELECT *
FROM Products
WHERE productId in  (SELECT productId  FROM Orders WHERE ShipVia = 3 and orderId = 10248)

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
FYI, the first error is that you used the keyword EXIST instead of the proper one EXISTS. This syntax is appropriate for EXISTS:
SELECT *
FROM Products
WHERE EXISTS (SELECT TOP 1 null FROM Orders WHERE Orders.productid = Products.productid)
;
Although, looking at your question, you query is incorrect anyway with having a hardcode shipvia value. If you are looking for all products that were shipped via the same method as orderid 10248 you first need to find the shipvia for orderid 10248, then the orders that also shipped using that shipvia then from there get the products on those orders.
 

-- get product data matching found product ids
SELECT *
FROM Products
WHERE ProductID IN (
   -- get product ids on orders that have same shipvia
   SELECT ProductID
   FROM Orders 
   WHERE ShipVia IN (
      -- get ship via of order id 10248
      SELECT ShipVia
      FROM Orders 
      WHERE OrderID = 10248
   ) 
);

Open in new window