Wayne schrieb:

>
> Hi All, I am trying to do the following SQL Statement.
>
> I have two tables,
>
> Rota & Rota_History, Both have identical fields.
>
> The Shift_date field in Rota is from 08/01/01 to 08/31/01
>
> The shift_date field in Rota_History goes from 01/01/01 to 07/31/01
>
> I am trying to do the following sql statement which is a count, but
> can't seem to use a normal join,
>
> I have tried this but it doesn't work either.
>
> Select count(*), employee_no from rota R, Rota_History H
> where R.position_employed = 556
> and R.Shift = 'C'
> and (R.on_day = 'OFF' and R.Shift_Date = '08/03/01') or (R.on_day
> = 'OFF' and R.Shift_Date = '08/04/01')
> or (R.shift_date = '08/02/01' and R.on_day = 'C') or (R.shift_date
> = '08/01/01' and R.on_day = 'C')
> or (H.shift_date = '07/31/01' and H.on_day = 'C') or (H.shift_date
> = '07/30/01' and H.on_day = 'C')
> or (H.shift_date = '07/29/01' and H.on_day = 'C') or (H.shift_date
> = '07/28/01' and H.on_day = 'C')
> group by employee_no
> having count(*) <= 8
> order by 1 desc, employee_no

Hmm, I little bit lost in all your and's and or's and parentheseses.
And I can't see where you're joining these tables (ie which field in H
is linked to which field in R).

Why do you think that a regular join doesn't work?

I'm sure it will work better than trying to join these tables in the
where clause...


Are you sure that sth like:

Select count(*), employee_no
from rota R,
[LEFT] JOIN Rota_History H ON ????
where

.... the list of your conditions ....

group by ...

does not work?



Luc.