I have three tables with the following structures:

users Table

vehicles Table

bids Table

I am trying to get all bids along with the ID of the vehicle being bid on, the name of the user_id who owns the vehicle, the name of the bidder_user_id who bid on the vehicle, and the bid amount.

My problem is that it seems I need to conduct two JOIN statements on the users table in order to match the bidder_user_id from bids, and vehicle.user_id from vehicles however that doesn't seem to be possible.

Here is my query so far:

SELECT
    vehicle_id AS vehicleID,
    vehicles.user_id AS sellerID,
    bids.bidder_user_id AS bidderID,
    CONCAT(users.first_name, ' ', users.last_name) AS bidderName,
    users.company AS bidderCompanyName,
    bids.amount AS bid_amount,
FROM `bids`
    JOIN `users` ON bids.bidder_user_id = users.id
    JOIN `vehicles` ON bids.vehicle_id = vehicle.id
ORDER BY vehicle_id DESC;

This is the result the above query produces:

However I would like the following result set which also includes the name of the seller as sellerName:

CodePudding user response：

Just join the users table twice:

SELECT bids.id, 
  bids.amount, 
  CONCAT(owners.first_name, ' ', owners.last_name) as owner, 
  CONCAT(bidders.first_name, ' ', bidders.last_name) as bidder
FROM bids
JOIN users as bidders
  ON bids.bidder_user_id = bidders.id
JOIN vehicles 
  ON bids.vehicle_id = vehicles.id
JOIN users as owners
  ON vehicles.user_id = owners.id