CODEWITHSUNDEEP
sqlsql-server
jadupl
jadupl

Reputation: 210

SQL Join condition clause

I have database with as diagram shows (see picture below) enter image description here

My task is to show total value of orders handled by each Employees. I have SQL statement:

SELECT e.FirstName,
       e.LastName,
       SUM(od.Quantity * od.UnitPrice * (1-od.Discount)) 
FROM Orders AS o
JOIN Employees AS e
ON o.EmployeeID = e.EmployeeID
JOIN [Order Details] AS od
ON o.OrderID = od.OrderID
GROUP BY e.FirstName,e.LastName

I have problem with further steps. I need to limited results only for those employees which:

I know It concern field ReportsTO in Employees table, but I don't know how get proper SQL clause. I am supposed to do with "EXISTS" or self-join ?

Thank You.

Upvotes: 2

Views: 60

Answers (3)

Ben Campbell
Ben Campbell

Reputation: 398

With a self join you can potentially create duplicates which would be difficult to debug due to the GROUP BY. EXISTS with a correlated sub query in the WHERE clause would be how I would accomplish this.

SELECT *
FROM Employees e
WHERE EXISTS(
  SELECT 1 
  FROM Employees _e 
  WHERE _e.ReportsTo = e.EmployeeID)

In your query:

SELECT e.FirstName,
       e.LastName,
       SUM(od.Quantity * od.UnitPrice * (1-od.Discount)) 
FROM Orders AS o
JOIN Employees AS e
ON o.EmployeeID = e.EmployeeID
JOIN [Order Details] AS od
ON o.OrderID = od.OrderID
WHERE /*NOT*/ EXISTS(
  SELECT 1 
  FROM Employees _e 
  WHERE _e.ReportsTo = e.EmployeeID)
GROUP BY e.FirstName,e.LastName

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use a self join to try to find someone reporting to him

SELECT e.FirstName,
       e.LastName,
       SUM(od.Quantity * od.UnitPrice * (1-od.Discount)) 
FROM Orders AS o
INNER JOIN Employees AS e    
   ON o.EmployeeID = e.EmployeeID
LEFT JOIN Employees as under  -- self join
   ON e.EmployeeID = under.ReportTo
INNER JOIN [Order Details] AS od
   ON o.OrderID = od.OrderID
GROUP BY e.FirstName,e.LastName

HAVING MAX(under.ReportTo) IS NULL     -- If doesnt find a match mean no one subordinate
   -- MAX(under.ReportTo) IS NOT NULL  -- mean have at least one subordinate

Upvotes: 1

Scott Dobbins
Scott Dobbins

Reputation: 294

Yes, Just use Exists.

  Where Exists(Select 1 from Employees where ReportsTo = e.EmployeeId)

Upvotes: 1

Related Questions