CODEWITHSUNDEEP
mysqlsqljoin
Guy Nording
Guy Nording

Reputation: 21

MySQL Left Join Multiple Rows

I have an interesting challenge... I have two tables, products and users.

products contains 2 columns, user_id and current_bidder, which hold two different IDs from the users table.

I would like to select all columns from products, and the name and rating from the users table for each user_id and current_bidder.

Essentially, I'm trying select columns from two different rows on a joined table, while disambiguating their names.

Any help would be greatly appreciated.

Upvotes: 2

Views: 2027

Answers (2)

John Woo
John Woo

Reputation: 263943

The nice way to avoid ambiguity between columns is to add an ALIAS on it.

SELECT  a.*,                   -- selects all records from products
        b.name AS user_name,   -- user_name is an alias of  users.name (user_id)
        c.name AS bidder_name  -- user_name is an alias of  users.name (current_bidder)
FROM    products a
        LEFT JOIN users b
            ON a.user_id = b.id
        LEFT JOIN users c
            ON a.current_bidder = c.id

The reason why I used LEFT JOIN is because I assumed that some products has no bidder yet. If INNER JOIN was used, product will never be shown on the result until there's a bidder on it.

Upvotes: 0

Don Kirkby
Don Kirkby

Reputation: 56240

Join to the user table twice, and give each copy a different alias. Something like this:

select p.name, p.weight, owner.name, bidder.name
from   product p
join   user owner
on     ...
join   user bidder
on     ...

Upvotes: 4

Related Questions