Reputation: 39
I have 2 entities Customers
and Agreements
.
Every customer could have 0..* agreements which could be active=1
or closed=0
.
I'm trying to select clients, who don't have any agreements (no records in table Agreements
) or clients who have agreements with flag 'closed' = 0 in one query.
The agreements table has a few million records.
Any suggestions how to do it the best way?
Upvotes: 0
Views: 62
Reputation: 1271231
I think you want exists
and not exists
, with this logic:
select c.*
from customers c
where not exists (select 1
from Agreements a
where a.customerid = c.customerid
) or
exists (select 1
from Agreements a
where a.customerid = c.customerid and
a.closed = 0
);
For performance, you want an index on agreements(customerid, closed)
.
Upvotes: 0
Reputation: 32031
use correlated subquery, i gused you a relational column customerid
select c* from customer c
where exists( select 1 from Aggrements a where c.customerid=a.customerid
and a.closed=0)
or (not exists ( select 1 from Aggrements a where c.customerid=a.customerid
and a.active=1)
)
if you dont have index on customerid column do it 1st otherwise query will take time
Upvotes: 1