left join doesn't seem to return 0's

  • Thread starter Thread starter James
  • Start date Start date
J

James

How can i get a list of customers that haven't placed an order in 90 days? I
have two tables, Customers and Orders. in the orders table i have a
CustomerID and a Orderdate Field. i have created a select statement that
returns the count of orders per customer in 90 days, but it never returns
the customer names that havn't placed an order yet. the strange thing is, if
i remove the where 90 day where clause, it does count the customers that
havn't placed an order.


SELECT Customers.CompanyName, Count(Order.OrderID) AS CountOfOrderID
FROM Customers LEFT JOIN [Order] ON Customers.CustomerID = Order.CustomerID
WHERE (((Order.Orderdate)>Date()-90))
GROUP BY Customers.CompanyName;
 
nm, just typing this out made me relize the solution.
WHERE (((Order.Orderdate)>Date()-90 Or (Order.Orderdate) Is Null))
 
acctualy i didn't find the solution, that new where clause i posted doesn't
work correctly. it will return only customers that have never created a
order. anyone have any ideas?




James said:
nm, just typing this out made me relize the solution.
WHERE (((Order.Orderdate)>Date()-90 Or (Order.Orderdate) Is Null))



James said:
How can i get a list of customers that haven't placed an order in 90
days? I have two tables, Customers and Orders. in the orders table i have
a CustomerID and a Orderdate Field. i have created a select statement
that returns the count of orders per customer in 90 days, but it never
returns the customer names that havn't placed an order yet. the strange
thing is, if i remove the where 90 day where clause, it does count the
customers that havn't placed an order.


SELECT Customers.CompanyName, Count(Order.OrderID) AS CountOfOrderID
FROM Customers LEFT JOIN [Order] ON Customers.CustomerID =
Order.CustomerID
WHERE (((Order.Orderdate)>Date()-90))
GROUP BY Customers.CompanyName;
Click to expand...
Click to expand...
 
A subquery is often the easiest way to ask for what's NOT there.

This kind of thing:

SELECT Customers.* FROM Customers
WHERE NOT EXISTS
(SELECT OrderID FROM Order
WHERE (Order.CustomerID = Customers.CustomerID)
AND (Order.OrderDate > Date() - 90));

If subqueries are new, this is Microsoft's introduction to them:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James said:
acctualy i didn't find the solution, that new where clause i posted
doesn't work correctly. it will return only customers that have never
created a order. anyone have any ideas?




James said:
nm, just typing this out made me relize the solution.
WHERE (((Order.Orderdate)>Date()-90 Or (Order.Orderdate) Is Null))



James said:
How can i get a list of customers that haven't placed an order in 90
days? I have two tables, Customers and Orders. in the orders table i
have a CustomerID and a Orderdate Field. i have created a select
statement that returns the count of orders per customer in 90 days, but
it never returns the customer names that havn't placed an order yet. the
strange thing is, if i remove the where 90 day where clause, it does
count the customers that havn't placed an order.


SELECT Customers.CompanyName, Count(Order.OrderID) AS CountOfOrderID
FROM Customers LEFT JOIN [Order] ON Customers.CustomerID =
Order.CustomerID
WHERE (((Order.Orderdate)>Date()-90))
GROUP BY Customers.CompanyName;
Click to expand...
Click to expand...
Click to expand...
 
that works thank you! but it takes a very long time to run. i have index's
and everything setup but it still takes a long time. any suggestions?


Allen Browne said:
A subquery is often the easiest way to ask for what's NOT there.

This kind of thing:

SELECT Customers.* FROM Customers
WHERE NOT EXISTS
(SELECT OrderID FROM Order
WHERE (Order.CustomerID = Customers.CustomerID)
AND (Order.OrderDate > Date() - 90));

If subqueries are new, this is Microsoft's introduction to them:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James said:
acctualy i didn't find the solution, that new where clause i posted
doesn't work correctly. it will return only customers that have never
created a order. anyone have any ideas?




James said:
nm, just typing this out made me relize the solution.
WHERE (((Order.Orderdate)>Date()-90 Or (Order.Orderdate) Is Null))



How can i get a list of customers that haven't placed an order in 90
days? I have two tables, Customers and Orders. in the orders table i
have a CustomerID and a Orderdate Field. i have created a select
statement that returns the count of orders per customer in 90 days, but
it never returns the customer names that havn't placed an order yet.
the strange thing is, if i remove the where 90 day where clause, it
does count the customers that havn't placed an order.


SELECT Customers.CompanyName, Count(Order.OrderID) AS CountOfOrderID
FROM Customers LEFT JOIN [Order] ON Customers.CustomerID =
Order.CustomerID
WHERE (((Order.Orderdate)>Date()-90))
GROUP BY Customers.CompanyName;
Click to expand...
Click to expand...
Click to expand...
 
nm, it works fast now for some reason... thanks again!


James said:
that works thank you! but it takes a very long time to run. i have index's
and everything setup but it still takes a long time. any suggestions?


Allen Browne said:
A subquery is often the easiest way to ask for what's NOT there.

This kind of thing:

SELECT Customers.* FROM Customers
WHERE NOT EXISTS
(SELECT OrderID FROM Order
WHERE (Order.CustomerID = Customers.CustomerID)
AND (Order.OrderDate > Date() - 90));

If subqueries are new, this is Microsoft's introduction to them:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James said:
acctualy i didn't find the solution, that new where clause i posted
doesn't work correctly. it will return only customers that have never
created a order. anyone have any ideas?




nm, just typing this out made me relize the solution.
WHERE (((Order.Orderdate)>Date()-90 Or (Order.Orderdate) Is Null))



How can i get a list of customers that haven't placed an order in 90
days? I have two tables, Customers and Orders. in the orders table i
have a CustomerID and a Orderdate Field. i have created a select
statement that returns the count of orders per customer in 90 days,
but it never returns the customer names that havn't placed an order
yet. the strange thing is, if i remove the where 90 day where clause,
it does count the customers that havn't placed an order.


SELECT Customers.CompanyName, Count(Order.OrderID) AS CountOfOrderID
FROM Customers LEFT JOIN [Order] ON Customers.CustomerID =
Order.CustomerID
WHERE (((Order.Orderdate)>Date()-90))
GROUP BY Customers.CompanyName;
Click to expand...
Click to expand...
Click to expand...
 
You could try a stacked query.

Create a query into the Orders table:
WHERE Order.OrderDate > Date() - 90
Save.

Then use the Unmatched Query wizard to get all the records from Customers
that have no match in that query. (It uses an outer join, where the query
returns a null OrderID.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James said:
that works thank you! but it takes a very long time to run. i have index's
and everything setup but it still takes a long time. any suggestions?


Allen Browne said:
A subquery is often the easiest way to ask for what's NOT there.

This kind of thing:

SELECT Customers.* FROM Customers
WHERE NOT EXISTS
(SELECT OrderID FROM Order
WHERE (Order.CustomerID = Customers.CustomerID)
AND (Order.OrderDate > Date() - 90));

If subqueries are new, this is Microsoft's introduction to them:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066


James said:
acctualy i didn't find the solution, that new where clause i posted
doesn't work correctly. it will return only customers that have never
created a order. anyone have any ideas?




nm, just typing this out made me relize the solution.
WHERE (((Order.Orderdate)>Date()-90 Or (Order.Orderdate) Is Null))



How can i get a list of customers that haven't placed an order in 90
days? I have two tables, Customers and Orders. in the orders table i
have a CustomerID and a Orderdate Field. i have created a select
statement that returns the count of orders per customer in 90 days,
but it never returns the customer names that havn't placed an order
yet. the strange thing is, if i remove the where 90 day where clause,
it does count the customers that havn't placed an order.


SELECT Customers.CompanyName, Count(Order.OrderID) AS CountOfOrderID
FROM Customers LEFT JOIN [Order] ON Customers.CustomerID =
Order.CustomerID
WHERE (((Order.Orderdate)>Date()-90))
GROUP BY Customers.CompanyName;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top