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.
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 ...
select p.blah as pblah, q.blah as qblah as OP is asking also desambiguating
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.
