Left join/inner join question

Databases
1

Hi Guys!

The following MySQL query is used to fetch auctions from a database and display the latest bid on the auction. However, if there are no bids on an auction, it will not return any results. How can I change the query so that rows are returned even if there are no bids (i.e. rows in the bids table matching this auction).

Thanks.


SELECT 
	dt_domains.id AS domain_id, 
	dt_bids.id, 
    dt_bids.domain, 
    dt_bids.maximum_bid, 
    dt_bids.display_bid,
    dt_bids.date, 
    dt_auctions.id AS auctionid, 
    dt_auctions.high_bid, 
    dt_auctions.currency, 
    dt_auctions.end_date, 
    dt_auctions.status 
FROM dt_bids 
    INNER JOIN (
		SELECT dt_bids.auction_id, max(dt_bids.id) AS lastdate
		FROM dt_bids
		GROUP BY auction_id
	) AS o ON dt_bids.auction_id = o.auction_id 
AND dt_bids.id = o.lastdate
	LEFT JOIN dt_auctions ON dt_auctions.id = dt_bids.auction_id
	LEFT JOIN dt_domains ON dt_domains.id = dt_auctions.domain_id 
    WHERE dt_domains.owner = '1' 
    GROUP BY dt_auctions.id
2

Make dt_auctions the main table in your query, and left join the bids table

3

you had the tables in the wrong order :slight_smile:

SELECT dt_domains.id AS domain_id
     , dt_bids.id
     , dt_bids.domain
     , dt_bids.maximum_bid
     , dt_bids.display_bid
     , dt_bids.date
     , dt_auctions.id AS auctionid
     , dt_auctions.high_bid
     , dt_auctions.currency
     , dt_auctions.end_date
     , dt_auctions.status 
  FROM dt_domains
LEFT OUTER
  JOIN dt_auctions 
    ON dt_auctions.domain_id = dt_domains.id
LEFT OUTER 
  JOIN ( SELECT auction_id
              , max(id) AS lastdate
           FROM dt_bids
         GROUP 
             BY auction_id ) AS o 
    ON o.auction_id = dt_auctions.id
LEFT OUTER
  JOIN bids
    ON bids.auction_id = o.auction_id
   AND bids.id = o.lastdate
 WHERE dt_domains.owner = '1'
4

guido, the domains table should be the main table, it might not have any auctions yet, let alone bids :slight_smile:

5

Ok. I didn’t get that reading the OP’s post, all he talks about is auctions that don’t have bids :slight_smile:

6

domains should still be the main table because of the WHERE condition :slight_smile: