Robert has given you a solution, but it is actually possible to do the
same thing just changing your original query from JOIN to LEFT JOIN
and moving everything referring to pchup from the WHERE clause to the
LEFT JOIN clause. The only ones you miss are those that aren't in the
empinfo_history table at all.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Clay Shannon" wrote:
> select h.fullname, s.shiftdate, p.pchin, p.pchout, s.shift_begin,
> s.shift_end
> from scheduled_work s
> join pchup p on p.empno = s.empno
> join empinfo_history h on h.empno = s.empno
> where (cast(p.pchin as date) = s.shiftdate) and
> ((cast(p.pchin as time) > s.shift_begin) or
> (cast(p.pchout as time) < s.shift_end))
> order by s.shiftdate
>
> but also need to see which records exist in scheduled_work which do
> not have corresponding records in pchup-in other words, the person
> did not punch in or out at all (did not show up for work on a day
> they were schedule to work).
>
> How to do?
>
> Clay Shannon,
>
> Dimension 4 Software