SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,048
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left join/inner join question

    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.

    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Make dt_auctions the main table in your query, and left join the bids table

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you had the tables in the wrong order
    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    guido, the domains table should be the main table, it might not have any auctions yet, let alone bids
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, the domains table should be the main table, it might not have any auctions yet, let alone bids
    Ok. I didn't get that reading the OP's post, all he talks about is auctions that don't have bids

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    domains should still be the main table because of the WHERE condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •