a small SQL query problem with SELECT

Question

SQL queries are still my weakest point, so here I am with yet another SQL question.

Imagine I have two tables: auctions and bids. Table auctions contains my auctions and table bids contains the list of bids for each auction.

Now I'm selecting values like this:

   SELECT
   `auction_title`,
   `auction_seo_title`,
   `auction_description_1`,
   `auction_unixtime_expiration`,
   `auction_startPrice`,
   MAX(`bids`.`bid_price`) as `bid_price`
   FROM
   `auctions`
   LEFT JOIN `bids` ON `auctions`.`auction_id`=`bids`.`bid_belongs_to_auction`
   ORDER BY
   `auction_unixtime_expiration`
   ASC
   LIMIT 5

The query works, but it's got a little catch to it: It selects only those auctions, which have at least one corresponding value inside the bids table. That means that if I have a new auction, which has no bids yet, the query doesn't return this auction, but I want it too!

I believe this is a very simple problem for anyone with at least above average SQL skills. I hope someone like that comes around :) Thanks in advance!

this may b problem due to LIMIT...did u try it without LIMIT or you should try with RIGHT JOIN — xkeshav
– xkeshav, Commented Mar 21, 2011 at 4:35
The query you posted is invalid, there is a missing GROUP BY clause. — The Scrum Meister
– The Scrum Meister, Commented Mar 21, 2011 at 4:35
Since you're using LEFT JOIN - you have to get all auctions as a result with bids related to it or padded with nulls. So look carefully one more time ;-) — zerkms
– zerkms, Commented Mar 21, 2011 at 4:36
I tried all LEFT JOIN, INNER JOIN and RIGHT JOIN, no change. Also, LIMIT is not a problem, I have got only 3 auctions in testing, so 5 isn't changing anything. — Frantisek
– Frantisek, Commented Mar 21, 2011 at 4:38
@RiMMER My guess is that you have a GROUP BY bids.bid_belongs_to_auction in your query... Also, LIMIT applies to all rows, so if you have 3 auctions with 10 bids each, the query will only return 1 auction with 5 bid rows. — The Scrum Meister
– The Scrum Meister, Commented Mar 21, 2011 at 4:41

Prisoner · Accepted Answer · 2011-03-21 04:48:50Z

1

SELECT
   `auction_title`,
   `auction_seo_title`,
   `auction_description_1`,
   `auction_unixtime_expiration`,
   `auction_startPrice`,
   MAX(`bids`.`bid_price`) as `bid_price`
FROM
   `auctions`
LEFT JOIN `bids` ON `auctions`.`auction_id`=`bids`.`bid_belongs_to_auction`
GROUP BY `auction_id`
ORDER BY `auction_unixtime_expiration` ASC

Give that a try. Assuming that works, you can add your LIMIT on to the end.

edited Mar 21, 2011 at 4:48

answered Mar 21, 2011 at 4:42

Prisoner

27.7k11 gold badges78 silver badges104 bronze badges

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

1 Comment

The Scrum Meister Over a year ago

GROUP BY goes before ORDER BY

Collectives™ on Stack Overflow

a small SQL query problem with SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related