have query based from a left join, now need to exclude records....

[andrewg]

Hey, my sql skills are passable but this is over my head i think....

I have a nice sql query as follows:

SELECT Job.id AS Job_id, Job.Order_Id, Job.StartDate, Job.Referral,
Job.Subject, Order.Id AS Order_Id, Order.isOrder, Order.SellingPrice,
Client.CompanyName, Client.email
FROM Client INNER JOIN ([Order] INNER JOIN Job ON Order.Id =
Job.Order_Id) ON Client.CustomerID = Order.CustId
WHERE (((Job.StartDate) Between #10/6/2006# And #11/6/2006#));


Now how would I EXCLUDE rows from this query based on rows which exist
in another table. For example, if a row exists in table
tblReminderSent I don't want that associated OrderID to show up in the
above query....

where do I go from here?

 

[John Spencer]

Assuming that you tblReminderSent has the OrderID in it you could use

WHERE NOT Exists(SELECT * FROM tblReminderSent WHERE tblReminderSent.OrderID
= Order.ID)
AND Job.StartDate Between #10/6/2006# And #11/6/2006#

OR you could join tblReminderSent to the query. I think that the query you
would need would look like the following.

SELECT Job.id AS Job_id, Job.Order_Id, Job.StartDate, Job.Referral,
Job.Subject, Order.Id AS Order_Id, Order.isOrder, Order.SellingPrice,
Client.CompanyName, Client.email
FROM (Client INNER JOIN ([Order]
INNER JOIN Job ON Order.Id =Job.Order_Id)
ON Client.CustomerID = Order.CustId)
LEFT JOIN tblReminderSent ON Order.ID = TblReminderSent.OrderID
WHERE Job.StartDate Between #10/6/2006# And #11/6/2006#
AND tblReminderSent.OrderID is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

I have a nice sql query as follows:

SELECT Job.id AS Job_id, Job.Order_Id, Job.StartDate, Job.Referral,
Job.Subject, Order.Id AS Order_Id, Order.isOrder, Order.SellingPrice,
Client.CompanyName, Client.email
FROM Client INNER JOIN ([Order] INNER JOIN Job ON Order.Id =
Job.Order_Id) ON Client.CustomerID = Order.CustId
WHERE (((Job.StartDate) Between #10/6/2006# And #11/6/2006#));

Now how would I EXCLUDE rows from this query based on rows which exist
in another table. For example, if a row exists in table
tblReminderSent I don't want that associated OrderID to show up in the
above query....

where do I go from here?

Add to your WHERE clause:

AND NOT EXISTS
(
SELECT *
FROM tblReminderSent AS R1
WHERE R1.Order.ID = Order.ID
);

Jamie.

--

 

[andrewg]

I have a nice sql query as follows:

SELECT Job.id AS Job_id, Job.Order_Id, Job.StartDate, Job.Referral,
Job.Subject, Order.Id AS Order_Id, Order.isOrder, Order.SellingPrice,
Client.CompanyName, Client.email
FROM Client INNER JOIN ([Order] INNER JOIN Job ON Order.Id =
Job.Order_Id) ON Client.CustomerID = Order.CustId
WHERE (((Job.StartDate) Between #10/6/2006# And #11/6/2006#));

Now how would I EXCLUDE rows from this query based on rows which exist
in another table. For example, if a row exists in table
tblReminderSent I don't want that associated OrderID to show up in the
above query....

where do I go from here?

Add to your WHERE clause:

AND NOT EXISTS
(
SELECT *
FROM tblReminderSent AS R1
WHERE R1.Order.ID = Order.ID
);

Jamie.

--

Thanks john and jamie, thats awesome!