I have tables as follows
Work
zone day
1 Tue
2 Thr
3 Mon
4 Thr
5 Thr
ZoneZip
zone zip
1 98006
1 98007
2 98027
3 98030
4 98040
5 98756
Customer
custNo zip
111 98027
123 98040
999 98006
888 98006
Task
custNo startDate
123 10/03/02
111 10/03/02
999 10/01/03
I need to get all the Tasks for a particular day along with the zone they
will be worked in. That's no problem. But, in addition I need all zones that
could be worked that day but don't have any tasks. That's hard.....
So if I look for Thr 10/03/02 my result should look like
ZoneZip.zone Task.custNo Task.startDate
2 111 10/03/02
4 123 10/03/02
5 null null
I think I am close with the query below but I get to many rows where custNo
and startDate are NULL.That is, I get a row for every memeber of the Work,
ZoneZip, Customer inner join but I really only want those for my particular
Work.day.
SELECT W.zone, T.CustNo, T.StartDate
FROM Work W LEFT JOIN ZoneZip Z ON (W.zone=Z.zone) LEFT JOIN Customer C ON
(C.Zip=Z.zip) LEFT OUTER JOIN Task T ON (C.CustNo=T.CustNo)
WHERE W.day = 'Thr'
AND (TO_DAYS(T.StartDate) = TO_DAYS('2002-10-03')
OR TO_DAYS(T.StartDate) IS NULL);
thanks
--
K. Brad Simmons
Secure Development
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
