| urgent query not yet solved!!!! [message #374670] |
Sat, 23 June 2001 23:26  |
debasish
Messages: 14
Registered: January 2001
|
Junior Member |
|
|
The problem with the queries I rcvd is that
with this query
SELECT SC FROM ORDER_TABLE WHERE PC IN (SELECT PC FROM ORDER_TABLE WHERE
SC='S2');
I will be able to view any supplier who is supplying atleast one part supplied by 'S2' but my query is
I want all the suppliers who r supplying ALL the parts supplied by supplier(scode) S2
ORDER_NO SC QTY_SUPPLIED O_DATE PC SUPPLY_DA
--------- -- ------------ --------- -- ---------
1 S1 300 12-JAN-97 P1 12-JAN-97
2 S1 200 12-JAN-97 P2 14-JAN-97
3 S1 400 15-JAN-97 P3 25-JAN-97
4 S1 200 18-FEB-97 P4 18-FEB-97
5 S1 100 22-MAR-97 P5 23-MAR-97
6 S1 100 25-MAR-97 P6 25-MAR-97
7 S2 300 28-MAY-97 P1 02-JUN-97
8 S2 400 23-JUN-97 P2 23-JUN-97
9 S3 200 23-OCT-97 P2 23-OCT-97
10 S4 200 14-NOV-97 P2 14-NOV-97
11 S4 300 18-DEC-97 P4
12 S4 400 18-JAN-98 P5 30-JAN-98
13 S5 300 18-FEB-98 P6
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374675 is a reply to message #374670] |
Sun, 24 June 2001 09:20  |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374676 is a reply to message #374670] |
Sun, 24 June 2001 09:22 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374677 is a reply to message #374670] |
Sun, 24 June 2001 09:23 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374678 is a reply to message #374670] |
Sun, 24 June 2001 09:28 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374679 is a reply to message #374670] |
Sun, 24 June 2001 09:29 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374680 is a reply to message #374670] |
Sun, 24 June 2001 09:29 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374681 is a reply to message #374670] |
Sun, 24 June 2001 09:30 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374682 is a reply to message #374670] |
Sun, 24 June 2001 09:30 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374683 is a reply to message #374670] |
Sun, 24 June 2001 09:30 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374684 is a reply to message #374670] |
Sun, 24 June 2001 09:30 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374685 is a reply to message #374670] |
Sun, 24 June 2001 09:30 |
Vishnu Murty
Messages: 16
Registered: June 2001
|
Junior Member |
|
|
try this one out----
select SC from Order_table ot
where
0< (select count(*) from
(select distinct PC from Order_Table
where SC = ot.SC
minus
select distinct PC from Order_Table
where SC = 'S2'))
and
0 =(select count(*) from
(select distinct PC from Order_Table
where SC = 'S2'
minus
select distinct PC from Order_Table
where SC = ot.SC))
|
|
|
|
| Re: urgent query not yet solved!!!! [message #374710 is a reply to message #374670] |
Mon, 25 June 2001 21:47 |
Arvind L
Messages: 27
Registered: June 2001
|
Junior Member |
|
|
There is one other way to get the required output,
select o1.sc from order_table o1,order_table o2
where o2.sc='S2' AND o1.sc<>'S2' AND o1.pc=o2.pc
group by o1.sc
having count(o1.sc) >=
(
select count(distinct(o3.pc))
from order_table o3
where o3.sc='S2'
)
Hope this will help you
-Arvind
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
