Mysql Join on table twice HELP!

Discussion in 'MySQL' started by wpfan, Oct 5, 2011.

0
  1. #1
    Im having issues trying to join a table twice.

    I have 3 tables: Users, Auctions, Bids

    Both Auctions and Bids contain a column called user_ID that relates to the Users table ID.

    The user_ID for each relates to the Seller(Auction Table) and Winner(Bids Table)

    How do I join all 3 so that I have a row with: auction_ID, auction_status, seller_name, seller_email, winner_name, winner_email

    Ive tried doing an inner join with no success.

    Table structure as follows:

    User Table: ID, user_login, user_email

    Bids Table: ID, user_ID, auct_ID, created, amount

    Auctions Table: ID, user_ID, title, end_time

    SO at the moment I have the following query:

    SELECT a.ID as auct_ID, a.status, s.user_email as seller, w.user_email as bidder, b.amount, b.created FROM auctions a INNER JOIN bids b ON b.auct_ID = a.id INNER JOIN users s ON a.user_ID = s.id INNER JOIN users w ON b.user_ID = w.id WHERE status='active' AND a.end_time < NOW() ORDER BY amount

    Which gives me all the bids of one auction that has expired. I want all auctions that have expired and only want the top bidders.
     
    wpfan, Oct 5, 2011 IP
  2. Technoslab

    Technoslab Peon

    Messages:
    46
    Likes Received:
    3
    Best Answers:
    3
    Trophy Points:
    0
    #2
    Where are seller_name,winner_name stored ? Are they in user_login column of user table ?
     
    Technoslab, Oct 6, 2011 IP
  3. wpfan

    wpfan Member

    Messages:
    64
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    31
    #3
    both the bidder i.e (the top bidder is the winner) and the seller details are stored in the users table
     
    wpfan, Oct 6, 2011 IP
  4. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #4
    Your going to need a couple sub-queries on the Bids table to determine the top bid. First sub-query determines the top bid by auction:

    SELECT MAX(amount) AS topbid, auct_ID FROM Bids;

    For the second sub-query you have to connect that back to the Bids table linking both those fields (topbid, auct_ID) to determine the ID of the top bidder. In that query you will pull in the auction id and the bidder id. Then you can use that data in your main query.

    My suggestion is to read up on sub-queries and take it a step at a time.
     
    plog, Oct 6, 2011 IP
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    You were on the right track. Something like this should give the top bids.

    
SELECT a.id         AS auct_id, 
       a.status, 
       s.user_email AS seller, 
       w.user_email AS bidder, 
       b.amount, 
       b.created 
FROM   auctions a 
       INNER JOIN bids b 
         ON b.auct_id = a.id 
       INNER JOIN users s 
         ON a.user_id = s.id 
       INNER JOIN users w 
         ON b.user_id = w.id 
WHERE  a.status = 'active' 
       AND a.end_time < NOW() 
ORDER  BY b.amount DESC
GROUP BY a.id;
    Code (markup):
     
    jestep, Oct 11, 2011 IP