CODEWITHSUNDEEP
sqlsql-serverjoinleft-join
Daniel
Daniel

Reputation: 35

Query two joins on the same value and table

I'm having trouble doing the following query. The idea is that I have two tables Stores and Users. In Stores I have the columns store_owners and store_last_modified, both values are integer that are related to the id of dbo.Users. How I can display the name that is stored in users related to the two columns. Like that:

select stores.name , users.name as name_store_owner , users.name as name_store_last_modified

from stores

LEFT JOIN users ON stores.store_owners=users.id (related to name_store_owner)
LEFT JOIN users ON stores.store_last_modified=users.id  (related to name_store_last_modified)

How do I do that?

Thank you in advance.

Upvotes: 1

Views: 58

Answers (2)

Coder1991
Coder1991

Reputation: 735

It appears that the condition can be checked without referring the table twice.

select * from
stores s
left join
users u on u.id = s.store_owners
and u.id = s.store_last_modified

From your question it appears that user id (id) should match with both the columns in the stores table for a single row.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need to give the tables aliases, so you can refer to the same table twice in the from clause. In addition, you need to refer to the right table (users not stores):

select s.name, uo.name as name_store_owner, um.name as name_store_last_modified
from stores s left join
     users uo 
     on s.store_owners = uo.id left join
     users um
     on s.store_last_modified = um.id

Upvotes: 2

Related Questions