I have a "date reference" table and an "events" table. Staff put their events in the event
table & then view a diary report.

Staff want ALL dates to be shown in the report, even if they haven't booked any events for
that date, so they can see they have a gap in the diary.


I have had a look in the newsgroups & DBISAM help & found the suggestion that a LEFT OUTER
JOIN ought to allow me to create such a result-set, but when I try it all I get are the
dates for which events have been booked:

Here's a simplified version of the SQL

SELECT
 DR.RefDate,
 Staff.FirstName + " " + Staff.LastName as StaffName,
 E.Memo,
 Type.Name as EventType

FROM
 DateReference DR
 LEFT OUTER JOIN Event E ON (E.DateEvent = DR.RefDate)
 LEFT OUTER JOIN Staff ON (E.EmpID = Staff.ID)
 LEFT OUTER JOIN Type ON (E.EventTypeID = Type.ID)

WHERE E.EmpID = 37
AND RefDate BETWEEN Current_Date - 1 AND Current_Date + 14

ORDER BY RefDate

--

Note that DateReference is a table that just contains 1 row for every date, plus
supporting data like the Day of Week, if its a holiday etc.

This SHOULD bring back 15 rows (as a minimum) one for each of the DR dates, with multiple
rows of date xx if there is more than 1 event ... however all I get is 1 row for each
Event record.

What I am doing wrong.

Adam

It's the "WHERE EMP ID" business. On an "empty date" that employee 37 had no
activity.

Change it to

WHERE (E.EmpID = 37) or (E.EmpID = null)

Also, you don't neet left outer joins for the last two joins. Just the join
to the reference date.

Robert

"adam" <adam@nospamplease.fmfoods.co.uk> wrote in message
news:67A66C47-7A64-46A9-9CC7-E1407FFCFAF6@news.elevatesoft.com...
>I have a "date reference" table and an "events" table.

adam


What happens if you leave out the other two joins?

Roy Lambert

Dear Robert,

Thank you for your help.

... Getting closer ... but not right!

I tried what you suggested & with a slight shift of parantheses it makes an improvement,
but not a perfect result.


--------

SELECT
 DR.RefDate,
 DR.DOW,
 IF(DR.RefDate = Current_Date then "TODAY" else "") as Today,
 Staff.FirstName + " " + Staff.LastName as StaffName,
 E.Memo,
 Type.Name as EventType

FROM
 DateReference DR
 LEFT OUTER JOIN Event E ON (DR.RefDate = E.DateEvent)
 LEFT JOIN Type ON (E.EventTypeID = Type.ID),
 Staff

WHERE (E.EmpID = 37 OR E.EmpID = null)
AND Staff.ID = 37
AND RefDate BETWEEN Current_Date - 1 AND Current_Date + 14



ORDER BY RefDate

---



Running the above on my trial dataset returns 11 records ... 3 with Event data, the other
8 without ... I am getting some but not all (!) the RefDate rows.

1. All the RefDate rows ARE present.
2. I have changed the code by changing the date range ... there are always a few missing
dates & the same ones are always missing.
3. Removing the WHERE (E.EmpID = 37 OR E.EmpID = null) brings back all the dates, with
none missing & multiple duplicate rows, for the different Event records ... this is the
correct behaviour, but without the ability to refine the query to just view the data for 1
member of staff accurately.

Can you see any other errors??!

Adam

Adam,

<< Can you see any other errors??!  >>

Why do you have a cartesian join on the Staff table ?

FROM
 DateReference DR
 LEFT OUTER JOIN Event E ON (DR.RefDate = E.DateEvent)
 LEFT JOIN Type ON (E.EventTypeID = Type.ID),
 Staff   <<<<<<<<<

Is there not a valid join condition between Staff and any of the other
tables ?

--
Tim Young
Elevate Software
www.elevatesoft.com

>Why do you have a cartesian join on the Staff table ?
>Is there not a valid join condition between Staff and any of the other
>tables ?

I want every row in the child dataset to contain the staff members name. If I don't use a
cartesian join the null children will have no staff name in the result set. As I am
searching by StaffName I only have 1 row returned from "Staff" so I don't get a cartesian
multiplication.

--

This is really getting my goat ... here is some SQL:

SELECT
 DR.RefDate,
 DR.DOW,
 IF(DR.RefDate = Current_Date then "TODAY" else "") as Today,
 Staff.FirstName + " " + Staff.LastName as StaffName,
 IF(E.Memo > "" then E.Memo else "") as Memo,
 Type.Name as EventType

FROM
 DateReference DR
 LEFT OUTER JOIN Event E ON (DR.RefDate = E.DateEvent)
 LEFT JOIN Type ON (E.EventTypeID = Type.ID),
 Staff

WHERE (E.EmpID = 37 OR E.EmpID = null)
AND Staff.ID = 37
AND RefDate BETWEEN Current_Date - 1 AND Current_Date + 14

ORDER BY RefDate

---


and I attach a ZIP file of 3 test tables ... can anyone figure out why the query is
droping rows??


Adam

adam

A small script - examining the data the problem was caused by the fact that the missing dates actually had entries BUT they had an EmpID other than 37 hence the where clause was zapping them



SELECT * INTO "Memory\Temp" FROM Event WHERE EmpID = 37;

SELECT
 DR.RefDate,
 DR.DOW,
 IF(DR.RefDate = Current_Date then 'TODAY' else '') as Today,
 Staff.FirstName + ' ' + Staff.LastName as StaffName,
 IF(E.Memo > '' then E.Memo else '') as Memo,
 Type.Name as EventType

FROM
 DateReference DR
LEFT OUTER JOIN "Memory\Temp"E ON (DR.RefDate = E.DateEvent)
 LEFT JOIN Type ON (E.EventTypeID = Type.ID),
 Staff

WHERE (E.EmpID = 37 OR E.EmpID = null)
AND Staff.ID = 37
AND RefDate BETWEEN Current_Date - 1 AND Current_Date + 14

ORDER BY  RefDate;


This should also work (at least it does in V4)

SELECT
 DR.RefDate,
 DR.DOW,
 IF(DR.RefDate = Current_Date then 'TODAY' else '') as Today,
 Staff.FirstName + ' ' + Staff.LastName as StaffName,
 IF(E.Memo > '' then E.Memo else '') as Memo,
 Type.Name as EventType

FROM
 DateReference DR
LEFT OUTER JOIN Event E ON (DR.RefDate = E.DateEvent) AND (E.EmpID = 37)
 LEFT JOIN Type ON (E.EventTypeID = Type.ID),
Staff

WHERE
Staff.ID = 37
AND RefDate BETWEEN Current_Date - 1 AND Current_Date + 14

ORDER BY  RefDate;

Roy Lambert

Adam,

<< I want every row in the child dataset to contain the staff members name.
If I don't use a cartesian join the null children will have no staff name in
the result set. As I am searching by StaffName I only have 1 row returned
from "Staff" so I don't get a cartesian multiplication. >>

Ahh, got it.

<< and I attach a ZIP file of 3 test tables ... can anyone figure out why
the query is droping rows?? >>

Would you mind emailing me the .zip ?  The attachment on the newsgroup is
all hosed up in OE.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Roy,

SINCERE thanks for this ... it was doing my head in & I couldn't get the space to figure
it out. I hope I can provide some payback at some point.

Adam

Tim


>Would you mind emailing me the .zip ? The attachment on the newsgroup is
>all hosed up in OE.
>

1. shouldn't you be able to access the database directly
2. upgrade to TMaN - it uses DBISAM you know

Roy Lambert