Select clients based on mutiple criteria

[chuchuchui]

Hello, using Crystal 8.5 here at an insurance company. Trying to select clients who have an auto and home policy but no life policy. Our different auto policies are represented by a 3, 24, 80, 82 and our homes are 5, 57, 61, 83, 87 then there's 73 which is a policy that is both. Our life policies are 53. I'm grouping by client, policies.clients_id.

 

[kskid]

I use Crystal 8.5 with an Oracle DB. I click on Database-> Show SQL Query and add the following at the end

AND NOT EXISTS
(SELECT *
FROM "POLICIES" POL
WHERE POL."POLICY_TYPE" = 53 AND
POL."CLIENT_ID" = CLIENT."CLIENT_ID")


Of course substitute the actual table name and fields from the example above.

Not sure if this could also be accomplished with an SQL Expression so I'll let lbass or snapsevampire address that.



Cheers,
-LW