COUNT DISTINCT problems

[NiallFH]

Hi all,

 

I'm sure I'm missing something quite simple here.

 

Basically, I have three tables. One for employees, one for dayshifts and one for nightshifts.

 

The structure is as follows:

 

employees - employeeID, employeename

weeks - weekID

dayshifts - dayshiftID, dayshiftemployeeID, dayshiftweekid

nightshifts - nightshiftID, nightshiftemployeeID, nightshiftweekid

 

I want to find a way to find out how many different employees I've used throughout a week.

 

I can easily do this for just dayshifts or just nightshifts, using the following code:

 

$get_employees_used = mysql_query("SELECT
COUNT(DISTINCT DayShiftEmployeeID) as total
FROM dayshifts 
WHERE DayShiftWeekID = $weekid
",$connection)

 

However, I can't find a way to combine both dayshifts and nightshifts together and return the number of employees I've used across all shifts in the the week.

 

Does anyone have any ideas?

[Barand]

I'd put them in a single table with a day/night field.

 

But until then

 

SELECT weekID, COUNT(DISTINCT empID) as employees
FROM
   (
   SELECT dayshiftweekid as weekID, dayshiftemployeeID as empID
   FROM dayshifts
   UNION
   SELECT nightshiftweekid as weekID, nightshiftemployeeID as empID
   FROM nightshifts
   )
GROUP BY weekID

[NiallFH]

Thanks for the reply Barand. I've tried this, to the letter, and I get the error - "

Every derived table must have its own alias"

 

Any ideas what I could be doing wrong?

[Barand]

Sorry, the subquery needs a table alias

 

SELECT weekID, COUNT(DISTINCT empID) as employees
FROM
       (
       SELECT dayshiftweekid as weekID, dayshiftemployeeID as empID
       FROM dayshifts
       UNION
       SELECT nightshiftweekid as weekID, nightshiftemployeeID as empID
       FROM nightshifts
       ) as shifts
GROUP BY weekID

[NiallFH]

Thanks that works great! Many thanks for your help!

[Psycho]

I second Brand's suggestion to use one table for the shifts and adding a new column to identify day vs. night. The above query may get you though the current problem, but you are only creating more headaches for yourself if you don't fix it now.