SQL Nested Queries with Parent Parameters

Question

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?

Conrad Frix · Accepted Answer · 2011-05-17 21:31:29Z

3

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

answered May 17, 2011 at 21:31

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Nested Queries with Parent Parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related