MS SQL subquery

user_11050464 0
Viewed 282

I'm a beginner in this, but I tried to select or create a subquery that will return the customers who purchased an item that is more expensive than the item (in the table) purchased item.

The select statement/subquery I created returns the data of literally all in the table except for the item even the prices lesser than his item, even though I specified the prices greater than the item. What seems to be wrong in my subquery?

subquery database-indexes sql-server sql
2 Answers

Seems like you could use a subquery like the following. I assume that if Badang bought multiple items you would want the purchase to be greater than their highest value purchase:

SELECT C.Cust_Name,
       I.ItemName,
       I.Price
FROM dbo.Customers C
     JOIN dbo.Orders O ON  C.CustomerID = O.CustomerID
     JOIN dbo.Items I ON O.ItemID = I.ItemID
WHERE I.Price > (SELECT MAX(Isq.Price)
                 FROM dbo.Customers Csq
                      JOIN dbo.Orders Osq ON Csq.CustomerID = Osq.CustomerID
                      JOIN dbo.Items Isq ON Osq.ItemID = Isq.ItemID
                 WHERE Csq.Cust_Name = 'Badang');
user_20299830

A slightly different way to think about it, means you only have to explicitly reference each table once (it doesn't remove them from the plan):

DECLARE @cust_name varchar(50) = 'Badang';

;WITH x AS 
(
  SELECT c.Cust_Name,
         i.ItemName,
         i.Price,
         max_price = MAX(CASE WHEN Cust_Name = @cust_name THEN i.Price END)
                     OVER (PARTITION BY c.CustomerID)
  FROM dbo.Customers    AS c
  INNER JOIN dbo.Orders AS o ON c.CustomerID = o.CustomerID
  INNER JOIN dbo.Items  AS i ON o.ItemID = i.ItemID
)
SELECT Cust_Name, ItemName, Price FROM x WHERE Price > 
(
  SELECT max_price FROM x WHERE Cust_Name = @cust_name
);

Also your tables really should have keys and clustered indexes; in its current state these queries are riddled with RID lookups.

user_613050
Related