|
View previous topic :: View next topic
|
| Author |
Message |
sharad_shanu
New User
Joined: 03 Oct 2006
Posts: 40
|
|
|
|
Hi all,
I am struggling with a query with selection.
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.
Thanks in advance. |
|
| Back to top |
|
 |
himanshu_pant
New User
Joined: 08 Jul 2014
Posts: 14
Location: India
|
|
|
|
Hi,
You could try the below query.
| Code: |
select distinct a.dist
from tab1 a
where 'o' = all
(select b.status
from tab1 b
where b.dist = a.dist)
|
Let me know if it helps. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Not Tested, please try..
| Code: |
Select a.dist,a.dealer
from table a,
where
a.dist NOT IN (b.dist
from table b
Where b.status <> 'O'
group by b.dist)
ANd a.status = 'O' |
|
|
| Back to top |
|
|
|
|
