CODEWITHSUNDEEP
sqlms-accessinner-join
Johanna Wood
Johanna Wood

Reputation: 13

How to JOIN Four tables, with muliple constraints

I have to write a SQL statement to show a sum for similar types, I also need add another exclusion constraint from another table.

This is using Microsoft Access 2010. The problem reads: Write an SQL statement to show the sum of HoursWorked for each Type of OWNER but exclude services of employees who have an ExperienceLevel of Junior and exclude any Type with less than three members.

 SELECT OWNER.Type, SUM(HoursWorked) AS Total_Work_Hours
FROM OWNER AS O, PROPERTY AS P, SERVICE AS S, EMPLOYEE AS E
WHERE O.OwnerID = P.OwnerID
AND P.Property= S.PropertyID
AND S.EmployeeID=E.EmployeeID
AND O.Type>3;

Upvotes: 1

Views: 242

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Learn to use proper, explicit, standard JOIN syntax. You also need a proper GROUP BY and should be qualifying all column references:

SELECT o.Type, SUM(HoursWorked) AS Total_Work_Hours
FROM ((OWNER AS O INNER JOIN
       PROPERTY AS P
       ON O.OwnerID = P.OwnerID
      ) INNER JOIN
      SERVICE AS S
      ON P.Property = S.PropertyID
     ) INNER JOIN
     EMPLOYEE AS E
     ON S.EmployeeID = E.EmployeeID
WHERE O.Type > 3 AND
      E.ExperienceLevel <> 'Junior'
GROUP BY o.Type
HAVING COUNT(*) >= 3;

Upvotes: 0

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

You may try this. 

SELECT O.[Type], SUM(e.HoursWorked) AS Total_Work_Hours,count(e.employeeid) as Total_Members
FROM OWNER AS O, PROPERTY AS P, SERVICE AS S, EMPLOYEE AS E
WHERE O.OwnerID = P.OwnerID
AND P.Property= S.PropertyID
AND S.EmployeeID=E.EmployeeID
AND e.ExperienceLevel NOT IN ('Junior')
Group by o.[Type]
Having count(e.employeeid)>=3;

Upvotes: 1

Related Questions