can't figure out what i'm doing wrong

[m0use]

OK, so this should be simple. But nothing I'm trying seems to be working. Three tables related to the problem.

Employee(PIN, firstName, lastName)
Late(ID, PIN, lateDate)
Sick(ID, PIN, sickDate)

All I want as the output is

Employee.Pin, Total Number of Times Late, Total Number of sick days


For some reason, the results for column 2 and 3 are always identical; Both fields are the higher of whichever column would be, if the answers were correct.

[orange]

What have you tried?

[m0use]

SELECT Employee.PIN, Count(Sick.PIN) AS SickBookoffs, Count(Late.PIN) AS LateBookoffs
FROM Employee INNER JOIN Sick ON Employee.PIN = Sick.PIN INNER JOIN Late ON Employee.PIN = Late.PIN
GROUP BY Employee.PIN;



this is the most recent code i have

[m0use]

also i've tried with LEFT and RIGHT JOINS instead, they don't seem to make a difference

[orange]

I think you need 2 queries 1 for Sick and 1 for Late. Or a sub query.
But you could use a UNION query such as

SELECT Employee.EmpId, Count(EmployeeDaysLate.DateLate) AS CountOfDateLate,""
FROM Employee INNER JOIN EmployeeDaysLate ON Employee.EmpId = EmployeeDaysLate.EmployeeId
GROUP BY Employee.EmpId

UNION

SELECT Employee.EmpId,"",Count(EmployeeDaysSick.DateSick) AS CountOfDateSick
FROM Employee INNER JOIN EmployeeDaysSick ON Employee.EmpId = EmployeeDaysSick.EmployeeId
GROUP BY Employee.EmpId

Union queries require the same number of fields in each Select statement.
The empty field "" is used to have 3 fields (syntactically) in each Select.

Good luck


There is subquery info here http://allenbrowne.com/subquery-01.html