SQL Query Subquery structure

88 views Asked by At

I'm having trouble with SQL. I have created a database of a restaurant and I am trying to create query that shows supervisors and employees who have served the same customer.

The Employee table has supervisor_id and employee_id. The Order table has the customer_id and the employee_id associated with that customer. I know that this involves a subquery and I know it would look something this.

SELECT DISTINCT
        Employee.employee_id,
        Orders.customer_id,
        Employee.supervisor_id
FROM    Employee,
        Orders
WHERE   EXISTS ( SELECT customer_id,
                        Employee.employee_id
                 FROM   Orders AS cs2,
                        Employee
                 WHERE  cs2.employee_id = Employee.employee_id
                        AND Employee.supervisor_id = Employee.employee_id
                        AND cs2.customer_id = Orders.customer_id );

As of right now, this query is not returning any results.

1

There are 1 answers

0
CrimsonKing On

Could be something like this:

SELECT a.employee_id, b.customer_id, a.supervisor_id
FROM   Employee a INNER JOIN Orders b ON a.employee_id = b.employee_id 
       INNER JOIN Orders c ON b.customer_id = c.customer_id
       AND c.employee_id = a.supervisor_id