Prev: Syntax Error
Next: error (-1104)
From: pol on 19 May 2010 09:25
I want to list all the employees from employee eventhough not existing in
holiday

SELECT
employee.company,
employee.recordid,
employee.employee,
employee.holsallow,
employee.hrs_holiday,
Holiday.datefrom,
Holiday.dateto,
Holiday.descr,
Holiday.ho_hrs,
Holiday.ho_mts,
FROM employee,
Holiday
WHERE ( Holiday.employeeid = employee.recordid )
order by Holiday.employeeid, Holiday.datefrom

Please help

With thanks
Pol


From: PsyberFox on 19 May 2010 10:02
Hi pol,

What i would normally do is change the FROM statement to the following:

FROM employee left join
holiday on Holiday.employeeid = employee.recordid

and obviously drop this from the WHERE statement

Hope this helps...
W

--
The Psyber Fox
http://www.psyberconsulting.co.za


"pol" wrote:

> I want to list all the employees from employee eventhough not existing in
> holiday
>
> SELECT
> employee.company,
> employee.recordid,
> employee.employee,
> employee.holsallow,
> employee.hrs_holiday,
> Holiday.datefrom,
> Holiday.dateto,
> Holiday.descr,
> Holiday.ho_hrs,
> Holiday.ho_mts,
> FROM employee,
> Holiday
> WHERE ( Holiday.employeeid = employee.recordid )
> order by Holiday.employeeid, Holiday.datefrom
>
> Please help
>
> With thanks
> Pol
>
>
From: John Spencer on 19 May 2010 10:19
SELECT
employee.company,
employee.recordid,
employee.employee,
employee.holsallow,
employee.hrs_holiday,
Holiday.datefrom,
Holiday.dateto,
Holiday.descr,
Holiday.ho_hrs,
Holiday.ho_mts,
FROM employee LEFT JOIN Holiday
ON (Holiday.employeeid = employee.recordid )
ORDER BY Holiday.employeeid, Holiday.datefrom

OR you might try the following

SELECT
employee.company,
employee.recordid,
employee.employee,
employee.holsallow,
employee.hrs_holiday,
Holiday.datefrom,
Holiday.dateto,
Holiday.descr,
Holiday.ho_hrs,
Holiday.ho_mts,
FROM employee,
Holiday
WHERE Holiday.employeeid = employee.recordid
OR employee.recordid is Not Null
order by Holiday.employeeid, Holiday.datefrom

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

pol wrote:
> I want to list all the employees from employee eventhough not existing in
> holiday
>
> SELECT
> employee.company,
> employee.recordid,
> employee.employee,
> employee.holsallow,
> employee.hrs_holiday,
> Holiday.datefrom,
> Holiday.dateto,
> Holiday.descr,
> Holiday.ho_hrs,
> Holiday.ho_mts,
> FROM employee,
> Holiday
> WHERE ( Holiday.employeeid = employee.recordid )
> order by Holiday.employeeid, Holiday.datefrom
>
> Please help
>
> With thanks
> Pol
>
>
 | 
Pages: 1
Prev: Syntax Error
Next: error (-1104)