Search code examples
sqldatabasedatabase-designdatabase-schema

How to query a relationship in sql

I am new to SQL and I came across an exercise that I can't seem to solve.

here we have the schema of the database

how can I get the names of all employees who live in the same city as the restaurant which they work for?

I tried to use the join query but since Restaurant doesn't have a foreign key that represents an employee I figured I had to go through the Works relation. How should I proceed?

Solution

  • You will need more than a single join. see it as a bridge join or intermediate join

    Since you have no direct link between your employee table and the restaurant one, you need to find a way. In your exemple the way is through the Work table which has EmployeeSSN and RestaurantID

    it should give you something like this :

    Select E.EmployeeSSN, R.RestaurantId
FROM Employee E
INNER JOIN WORKS W
ON E.EmployeeSSN = W.EmployeeSSN
INNER JOIN Restaurant R
ON W.RestaurantID = R.RestaurantID
WHERE E.city = R.city