I have found that when I want to restrict a query using exists, if I group by in the exists, it either doesn't return anything or gets too many. Here is an example using northwind. If you run the exist query, there are 4 rows out of 6 total. Using the exists, none are returned. I found that some queries like this select the exists select without the aggregate, so I get all 6 instead of the 4 desired.
SELECT
Orders.ShipRegion, Orders.CustomerIDFROM
Orderswhere
exists(SELECT
Orders2.OrderIDFROM
Orders Orders2INNER
JOIN Customers ON Orders.CustomerID = Customers.CustomerIDWHERE
Region = 'SP'AND
Orders.OrderID = Orders2.OrderIDgroup
by Orders2.OrderIDhaving
count(Orders2.OrderID) > 6)
using exists with group by
Victor_123
Hi Steve,
Presumably you are trying to count the number of orders per customer The statement counts the number of customers per orderid and that's going to give you a count of 1 (it's a unique identifier):
USE Northwind
SELECT Orderid, COUNT(OrderID)
FROM Customers C
INNER JOIN Orders O
ON O.CustomerID = C.CustomerID
GROUP BY OrderID
HAVING COUNT(orderid) > 1
To get the count of orders per customer use:
SELECT O.CustomerID, COUNT(O.CustomerID)
FROM Customers C
INNER JOIN Orders O
ON O.CustomerID = C.CustomerID
GROUP BY O.CustomerID
HAVING COUNT(O.CustomerID) > 1
A possible alternative you could use would be a query to determine the required data in the FROM caluse:
SELECT DISTINCT O.ShipRegion,
O.CustomerID
FROM Orders O
INNER JOIN (SELECT O.CustomerID
FROM Customers C
INNER JOIN Orders O
ON O.CustomerID = C.CustomerID
WHERE C.Region = 'SP'
GROUP BY O.CustomerID
HAVING COUNT(O.CustomerID) > 1) CO
ON O.CustomerID = CO.CustomerID
This filters out the customers without orders in the inner query.
The optimal query would be:
SELECT C.Region, O.CustomerID
FROM Customers C
INNER JOIN Orders O
ON O.CustomerID = C.CustomerID
WHERE C.Region = 'SP'
GROUP BY C.Region, O.CustomerID
In the end, if you out them all together you get:
SELECT O.ShipRegion, O.CustomerID, O.OrderDate< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
FROM Orders O
WHERE EXISTS(
SELECT O2.OrderID
FROM Orders O2
INNER JOIN Customers C
ON O2.CustomerID = C.CustomerID
INNER JOIN
(
SELECT O.CustomerID
FROM Customers C
INNER JOIN Orders O
ON O.CustomerID = C.CustomerID
WHERE C.Region = 'SP'
GROUP BY C.Region, O.CustomerID
HAVING COUNT(OrderID) > 6) C2
ON C.CustomerID = C2.CustomerID
WHERE O.OrderID = O2.OrderID
GROUP BY O2.OrderID
)
Max