Accounts with No Orders in Timeframe-CodePudding

I'm trying to create a SQL query where I get the results of what Accounts have had no Orders for a given Business.

So I want to filter on the Business level, and there can be multiple businesses and all have unique accounts to them.

The database relationship could basically be looked at like this:

I've tried this with a test business account, however, I'm not getting the results I wanted:

SELECT DISTINCT(A.name)
FROM Accounts AS A
LEFT JOIN `Token` AS T ON T.account_id = A.id
WHERE NOT EXISTS (SELECT 1 FROM Orders AS O WHERE O.account_id = A.id)
AND T.business_id = 1

I was also hoping I could just do a WHERE check on the Orders if business_id = 1, but that didn't give me anything different.

CodePudding user response：

Why do you need the Token table in this query? If you only want accounts with no orders then you could do it like this:

SELECT A.name FROM Accounts A
WHERE NOT EXISTS( SELECT O.id FROM Orders O WHERE O.account_id = A.id AND O.business_id = 1 )

CodePudding user response：

Hello You can test this:

SELECT A.name
FROM Accounts AS A
LEFT JOIN Orders AS O ON A.id = O.account_id
LEFT JOIN Businesses AS B ON B.id = O.business_id
GROUP BY A.name
HAVING COUNT(O.id) = 0;