Drs Filter for Groups

[2can2]

Hi, I am trying to write a filter that checks for Debtors that don't have a CN_Group record. It seems to be an issue to have linked tables to the DB_Main or I can't get the syntax right.
The Sql query is simple but how would I add this to my filter?
SELECT CN_GroupLink.GroupID
FROM CN_GroupLink RIGHT OUTER JOIN
DB_Main INNER JOIN
CN_Main ON DB_Main.DebtorID = CN_Main.DebtorID ON CN_GroupLink.ProspectID = CN_Main.ProspectID
WHERE (CN_GroupLink.GroupID IS NULL)
ORDER BY DB_Main.AccountNo

I have used links to another table successfully in filters but not a 3rd table? 2 table example :
SO_Main.Status=0 AND (SELECT COUNT(EmailLogID) FROM SO_EmailLog WHERE SO_EmailLog.InvoiceID = SO_Main.InvoiceID ) = 1

I tried several variations of the below filter but the Joins seem to be my issue :
(Select GroupID FROM CN_GroupLink WHERE CN_GroupLink.ProspectID = CN_Main.ProspectID AND CN_Main.DebtorID = DB_Main.DebtorID) = 'af73293e531c46b8a9ea'

Any help would be appreciated. Cheers

[perry]

Hi Doug,

You forgot CN_Main in your query

EXISTS(SELECT dbo.CN_Main.DebtorID FROM dbo.CN_GroupLink INNER JOIN dbo.CN_Main ON dbo.CN_GroupLink.ProspectID = dbo.CN_Main.ProspectID AND dbo.CN_Main.DebtorID = DB_Main.DebtorID AND CN_GroupLink.GroupID = 'af73293e531c46b8a9ea')