Creating an OR style join in 3 tables 1

[RobNauticus]

Hello,

I have 3 tables with 3 linked fields. Actually there is only one "table" the other 2 are views of the same table.

Here they are:

Datelogtable)
(PK)JobID
(PK)LotID
(PK)TaskID
TaskDate

r_LathAll (query)
JobID
LotID
TaskID=1
TaskDate

r_SheetrockAll (query)
JobID
LotID
TaskID=22
TaskDate



I want to be able to see results for matches in one query or another not both. Is there a way to get them to join this way? An equal join does not work because it would need a match in both queries. A left join on both does not work because it will return all records in the DateLog table.

I am sure the answer is simple but it escapes me.

Can anyone guide me?

THANKS!
Rob

 

[PHV]

something like this ?
SELECT *
FROM (Datelog AS D
LEFT JOIN r_LathAll AS L ON D.JobID=L.JobID AND D.LotID=L.LotID)
LEFT JOIN r_SheetrockAll AS S ON D.JobID=S.JobID AND D.LotID=S.LotID)
WHERE (L.JobID Is Null OR S.JobID Is Null)
AND (L.JobID & S.JobID) Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

Good deal, makes sense, will put it to work.

Thanks!