Selecting records from 2 tables where a value appears less than a certain amount

[rayman0487]

OK, so I've been working on coding this issue for a while now and I can't seem to get the SQL query correct. Basically, I have 2 tables - Credentials and Customers.

 

Credentials fields: id, Closer

Customer fields: id, Progress, Closer

 

In credentials, Closer is set to 0,1,2 depending on the type of user and then in Customers the Closer field is assigned a user id (id field from credentials). I'm trying to select ALL the ids from credentials that are only assigned to 5 or less customers that have a progress of 1. The issue is that if the closer has 0 assigned customers the code doesn't work. For example,

SELECT id FROM Credentials WHERE  Closer>0  AND EXISTS (SELECT Closer FROM Customers WHERE Progress='1' GROUP BY Closer HAVING Count(Closer)<'5')

Works when a closer has atleast 1 customer. The problem is that we have a CRON job set to run every night at midnight that

SET Customers.Closer='REM' WHERE Customers.Progress='1'

so it is impossible to set the first customer because it doesn't get any ids from Credentials.

 

Is there an easy way to do this other than first testing if the user has an assigned customer? I'm trying to be efficient and not query a ton of times.

 

Hopefully someone can make sense of this rambling, if you need anything clarified, please let me know.

 

TYIA,

Ray

[xyph]

To avoid confusion, in the customer table, the closer column should be credential_id. The relationship becomes much more obvious. I'm going to refer to it as that in my reply.

 

I have no idea what the CRON does, so I'm just going to ignore it. It seems to corrupt the data we're querying, so you'll have to explain it in more detail if the solution I provide doesn't work.

 

SELECT credential_id
FROM customers
WHERE progress = 1
GROUP BY credential_id
HAVING COUNT(*) <= 5

UNION ALL

SELECT cred.id
FROM credentials cred
WHERE NOT EXISTS (
  SELECT cust.credential_id FROM customers cust WHERE cust.credential_id = cred.id
)

 

I think that's what you want. It grabs the IDs WITH 5 or less rows, then merges those with a query that does an 'anti-join' of sorts.