CODEWITHSUNDEEP
mysqlsqlselect
Frantisek
Frantisek

Reputation: 7693

a small SQL query problem with SELECT

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!

Upvotes: 3

Views: 212

Answers (1)

Prisoner
Prisoner

Reputation: 27618

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.

Upvotes: 1

Related Questions