Reputation: 21
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
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
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