I have a distributor no with me (say 0001). Now this distributor can have multiple dealer like X001, X002 and so on. Each of these dealers can have one of the status like Open (O), closed (C), pending (P), Hold (H).
So one distributor can have any number of dealer whereas each dealer can have one of the status. So it looks like:
dist dealer status
0001 X001 C
0001 X002 H
0001 X003 P
0001 X004 H
0001 X005 O
0001 X006 O
0001 X007 P
0002 X001 O
0002 X002 O
0003 X001 C
0003 X002 H
Now I have the dist no and I wish to ascertain if there are only open dealer associated with it. I mean if there are any closed or held or pending dealers we should not select it.
A distributor can have several dealers but none of them should have status other than O.
In above data only distributor 0002 satisfies the condition.
I am struggling to figure out the query. Therefore calling for a help.
I am avoiding "NOT IN ('P', 'C', 'H')". Basically I want to use only 'O' in the query. Lets assume that there could be other codes/spaces as well. What I need to know that a distributor should have open dealers.