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

I hope I am making some sense,

Thanks in advance

Wayne