CODEWITHSUNDEEP
sqlsql-server
Umair A.
Umair A.

Reputation: 6873

SQL Nested Queries with Parent Parameters

I need to write a query of the scenario but I am lost writing it.

Assume there are two tables Items and Bids. The items are being selected via some sort of filter

SELECT i.* FROM Items i WHERE i.Id = 2

Now there is a Bids table having "ItemId" column to link Items to Bids. Now I want all items' data with HighestBid, LowestBid and TotalBids and I am trying this but it's not working.

SELECT i.*, hal.* 
FROM Items i, (SELECT MAX(b.OfferAmount), MIN(b.OfferAmount), COUNT(b.*) FROM Bids b WHERE b.ItemId = i.Id) As hal
WHERE i.Id = 2

Is there something wrong with this?

Upvotes: 0

Views: 2184

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

Try this

SELECT i.*, 
       hal.* 
FROM   items i 
       INNER JOIN (SELECT MAX(b.offeramount), 
                          MIN(b.offeramount), 
                          b.itemid, 
                          COUNT(b.*) 
                   FROM   bids b 
                   GROUP  BY itemid) AS hal 
         ON i.Id= hal.itemid 
WHERE  i.id = 2

Upvotes: 3

Related Questions