CODEWITHSUNDEEP
mysqlduplicates
jonathan young
jonathan young

Reputation: 237

MySQL count dates from 2 tables but not duplicate dates

I have 2 table in mysql 1 table with public holidays in:

id   empid      date
1      10     2015-10-17
2      10     2015-12-24
3      10     2016-01-01
4      11     2015-10-17
5      11     2015-12-24
6      11     2016-01-01

I have a second table with holidays in 

id    employeeid           date
1      10               2015-10-15
2      10               2015-10-16
3      10               2015-10-17
4      10               2015-10-18
5      11               2015-11-11

The question is how do I join both tables together and do a count with out counting any duplicate dates (dates that are in both tables) the count should be 6 days holiday because the 2015-10-17 is in both tables for employee id 10. I have multiple employee id and dates in the table .

employee 10 count 6
employee 11 count 4

I have tried inner join but can not work it out

Upvotes: 0

Views: 35

Answers (1)

John Ruddell
John Ruddell

Reputation: 25852

select count(t.u_date) as employee_count, t.employee_id
from (
    select date as u_date, employee_id
    from table

    union

    select date as u_date, employee_id
    from table
) as t
group by t.employee_id

FIDDLE_EXAMPLE

EDIT:

for your updated query you can just do this

SELECT 
    ...
FROM employees AS e
LEFT JOIN 
(   SELECT empid, date, hours
    FROM holidays_taken
    UNION
    SELECT empid, date, hours
    FROM public_holidays
) AS h ON e.id = h.empid
WHERE e.id = 1
GROUP BY Year

Upvotes: 1

Related Questions