recursive query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

recursive query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-17 : 07:10:02

ng writes "


Table Suppliers					
SNo     SNAME  STATUS  CITY		
S1  	Smith      20  London	
S2  	Jones      10  Paris		
S3  	Blake      30  Paris		
S4  	Clark      20  London	
S5  	Adams      30  Athens	
Table Parts
PNo     PNAME  COLOR   WEIGHT  CITY
P1  	Nut    Red        12  London
P2  	Bolt   Green      17  Paris
P3  	Screw  Blue       17  Rome
P4  	Screw  Red        14  London
P5  	Cam    Blue       12  Paris
P6  	Cog    Red        19  London
Table Projects
JNo     JNAME     CITY
J1  	Sorter    Paris
J2  	Puncher   Rome
J3  	Reader    Athens
J4  	Console   Athens
J5  	Collator  London
J6  	Terminal  Oslo
J7  	Tape      London
Table Shipments
SNo	PNo	JNo	QTY
S1  	P1  	J1  	200
S1  	P1  	J4  	700
S2  	P3  	J1  	400
S2  	P3  	J2  	200
S2  	P3  	J3  	200
S2  	P3  	J4  	500
S2  	P3  	J5  	600
S2  	P3  	J6  	400
S2  	P3  	J7  	800
S2  	P5  	J2  	200
S3  	P3  	J1  	200
S3  	P4  	J2  	500
S4  	P6  	J3  	300
S4  	P6  	J7  	300
S5  	P2  	J2  	200
S5  	P2  	J4  	100
S5  	P5  	J5  	500
S5  	P5  	J7  	100
S5  	P6  	J2  	200
S5  	P1  	J4  	100
S5  	P3  	J4  	200
S5  	P4  	J4  	800
S5  	P5  	J4  	400
S5  	P6  	J4  	500

The names and identifiers of the suppliers who supply at least all the
parts that are supplied by supplier S2.
Please help me............................

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-17 : 09:27:04

What course is this for?

Brett

8-)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-17 : 21:47:38

quote:
Originally posted by X002548

What course is this for?

Brett

8-)

ha ha ha, and not even a starting query to work with.

--------------------
keeping it simple...

shreyas
Starting Member

2 Posts

Posted - 2004-12-14 : 07:13:07

You can run the query given below to get the output.

select b.sno from (select pno from shipments where sno ='s2' group by sno, pno ) a
inner join (select sno, pno from shipments where sno <>'s2'
group by sno, pno) b on a.pno = b.pno
group by b.sno
having count(*) = (select count(distinct(pno)) from shipments where sno ='s2' )

Vishal Malhotra

quote:

Originally posted by AskSQLTeam

ng writes "


Table Suppliers					
SNo     SNAME  STATUS  CITY		
S1  	Smith      20  London	
S2  	Jones      10  Paris		
S3  	Blake      30  Paris		
S4  	Clark      20  London	
S5  	Adams      30  Athens	
Table Parts
PNo     PNAME  COLOR   WEIGHT  CITY
P1  	Nut    Red        12  London
P2  	Bolt   Green      17  Paris
P3  	Screw  Blue       17  Rome
P4  	Screw  Red        14  London
P5  	Cam    Blue       12  Paris
P6  	Cog    Red        19  London
Table Projects
JNo     JNAME     CITY
J1  	Sorter    Paris
J2  	Puncher   Rome
J3  	Reader    Athens
J4  	Console   Athens
J5  	Collator  London
J6  	Terminal  Oslo
J7  	Tape      London
Table Shipments
SNo	PNo	JNo	QTY
S1  	P1  	J1  	200
S1  	P1  	J4  	700
S2  	P3  	J1  	400
S2  	P3  	J2  	200
S2  	P3  	J3  	200
S2  	P3  	J4  	500
S2  	P3  	J5  	600
S2  	P3  	J6  	400
S2  	P3  	J7  	800
S2  	P5  	J2  	200
S3  	P3  	J1  	200
S3  	P4  	J2  	500
S4  	P6  	J3  	300
S4  	P6  	J7  	300
S5  	P2  	J2  	200
S5  	P2  	J4  	100
S5  	P5  	J5  	500
S5  	P5  	J7  	100
S5  	P6  	J2  	200
S5  	P1  	J4  	100
S5  	P3  	J4  	200
S5  	P4  	J4  	800
S5  	P5  	J4  	400
S5  	P6  	J4  	500

The names and identifiers of the suppliers who supply at least all the
parts that are supplied by supplier S2.
Please help me............................

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources