Subquery To Select Checks by Varying Age Requirements

[AlexB123]

Hi all,

I have a need to select all checks that fall within an age threshold determined by a client's preference. For example, one client might choose a 30 day threshold, another might choose every ninety days.

I am receiving an error: "at most one record can be returned by this subquery."

Open to any suggestions, thanks.

SELECT tblChecks.CheckID
FROM (tblChecks 
 INNER JOIN tblClients ON tblChecks.ClientID = tblClients.ClientID)
 INNER JOIN tbl_UC_AGE ON tblClients.MV_UC_AGE = tbl_UC_AGE.ID
WHERE ((tblChecks.ActionID)=2000)
AND ((tblChecks.Status)=1)
AND ((tblChecks.Check_Date)<=DATE() - (SELECT a.AGE_DAYS FROM tbl_UC_AGE a
             INNER JOIN tblClients b ON a.ID = b.MV_UC_AGE))
;

 

[xenou]

Did you check out what this subquery is returning?

SELECT a.AGE_DAYS FROM tbl_UC_AGE a
INNER JOIN tblClients b ON a.ID = b.MV_UC_AGE

It looks like it is returning a set of every UC_age for every client. Which is probably wrong (you need a UC_age for each particular client).
Might want to just start over on this one.

Not sure what your data looks like. Probably should be okay to use a regular join, not a subquery. You should post some sample data if you are stuck - hard to write queries without knowing what is in the tables.

 

[AlexB123]

@xenou,

Thank you for your reply. I was overthinking the problem, I just relied on the innerjoin.

SELECT tblChecks.CheckID
FROM (tblChecks 
 INNER JOIN tblClients ON tblChecks.ClientID = tblClients.ClientID)
 INNER JOIN tbl_UC_AGE ON tblClients.MV_UC_AGE = tbl_UC_AGE.ID
WHERE ((tblChecks.ActionID)=2000)
AND ((tblChecks.Status)=1)
AND ([LEFT][COLOR=#222222][FONT=Verdana]DATE() [/FONT][/COLOR][/LEFT]- (tblChecks.Check_Date)>= tbl_UC_AGE.Days)