Mysql Join on table twice

Question

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 bidder.

can you please post the CREATE statements for all three tables so that it's easier to see? — imm

Tudor Constantin Tudor Constantin · Accepted Answer · 2011-10-06T04:12:36

Try with:

SELECT a.*, s.*, w.*, b.* FROM auctions a
INNER JOIN bids b ON b.auction_id = a.id
INNER JOIN users s ON a.user_ID = s.id 
INNER JOIN users w ON b.user_ID = w.id 
WHERE .....
ORDER BY b.amount DESC LIMIT 1

the s and w table alias is for sellers and winners

Mysql Join on table twice

1 Answers