Query on Multiple Tables with Recursion On One Table

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

SQL Server Development (2000)

Query on Multiple Tables with Recursion On One Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-03 : 10:00:04

Robert writes "I have a database with three tables that are similar in structure to the solution graz offered in post
http://www.sqlteam.com/item.asp?ItemID=1333. Two tables have a many-to-many relation through a third table. If I try to generalize the problem, I'll just confuse myself, so let me just give my specific example.

Tables:


Products      ProductVendors   Vendors
--------      --------------   -------
ProductID     ProductID        VendorID
ProductDesc   VendorID         VendorName
OnHand                         VendorAddress

Each product is assembled from many parts, and each part is purchased from several vendors. A vendor can supply more than one part for any given product.

I need a query that, given a vendor ID, will return a cartesian product of all the product info for which the vendor supplies parts AND all the vendors (and their info) that supply parts for those products.

Let's say the tables are populated like this:

Products:
1   'Widget'   23
2   'Didget'   16
3   'Gidget'   38

ProductVendors:
1   2
1   3
2   1
2   3
3   1
3   2
3   3

Vendors:
1   'Acme, Inc.'   'Somewhere, USA'
2   'Parts, LTD'   'Nowhere, UK'
3   'FlyByNite'    'Whonoes, USA'

What query will let me supply WHERE VendorID = 2 that will return the following recordset?


ProductID  ProductDesc  VendorID  VendorName
1          'Widget'     2         'Parts, LTD'
1          'Widget'     3         'FlyByNite'
3          'Gidget'     1         'Acme, Inc.'
3          'Gidget'     2         'Parts, LTD'
3          'Gidget'     3         'FlyByNite'

I've tried SELECT P.ProductID, P.ProductDesc, V.VendorID, V.VendorName FROM Products P INNER JOIN ProductVendors PV ON P.ProductID = PV.ProductID JOIN Vendors V ON PV.VendorID = V.VendorID WHERE VendorID = 2 Order By P.ProductID, but this only returns the two rows for vendor 2.

Also, I know I should be able to use relational algebra or a similar tool to figure out this query. Is there a good web site or book that teaches you to write complex queries based on a knowledge of relational algebra?

Thanks in advance!"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-03 : 10:43:24

Hi Robert,
This one is not so bad. First use a derived table to figure out which product Vendor 2 deals in. Then join that to the rather simple query joining the three table together.

Select A.Productid,
A.ProductDesc,
C.VendorID,
C.VendorName
FROM #Products A
JOIN #ProductVendors B
ON A.Productid = B.Productid
JOIN #Vendors C
ON C.VendorID = B.VendorID
JOIN (SELECT Productid
FROM #ProductVendors
Where VendorID = 2) AS D
ON A.ProductID = D.ProductID
Order by 1,3

Salty
Starting Member

1 Post

Posted - 2002-01-03 : 14:57:01

Thank you, Todd. Eezee for you, señior, deeficult for mee.

Here's some follow-up questions:

Your SELECT query works as you say only after I remove the # mark from the table names. I understand that this implies temporary tables, and I got the errors because I have not created temp tables by these names. Since the SELECT seems to work without the temp's, why use them? In my case, I am extremely limited in disk-space (we're using a hired SQL Server with only 85 MB total - 50 MB DB space and 35 MB transaction log -- and no, I did not set it up this way), so this would be best if it can work without using temps.

I'm grateful your solution works, but I don't understand exactly why it works. How did you come up with the solution, and how can I come up with such complex queries on my own in the future, without having to run to the SQLTeam every time? I do appreciate your giving me the fish, but now I'd like to learn how to use my own fishing pole and tackle.

What is the "1, 3" in your Order By? Why not order by column names?

Thanks again!

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-03 : 16:08:26

The Temp tables are simply a results of the fact that they are what I put your test data in. I should have remo\ved them before I posted.

I basically did this like this. Define the Problem in the simplest terms possible: "Give me all the Products and Supliers info for all the products suplied by Vendor 2."

Break up the Problem if you can. I started with, "the products supllied by supplier 2" This boils down to the simple select statement:
SELECT Productid
FROM #ProductVendors
Where VendorID = 2

Tackle the next part, "Give me all the Products and Supliers". This is a pretty simple select statement:
Select A.Productid, A.ProductDesc, C.VendorID, C.VendorName
FROM #Products A
JOIN #ProductVendors B ON A.Productid = B.Productid
JOIN #Vendors C ON C.VendorID = B.VendorID

Then ask yourself how they go together. In this case, the first select is meant to limit the records returned by the second. You can do this in a where clause or with an Inner Join. I chose to use the first select as a derived table and join to it. There is an article arround here about derived tables.
cont.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-03 : 16:11:17

My best advice for sql fishing is to follow sites like this one. Read the articles. Try to solve other peoples problems on your own and compare them with others solutions. This will really help you to define problems in a way that you can use sql to solve them.

BTW - My best advice for real fishing is bring plenty of

for

.

THe order by thing was just laziness. Saving a few keystrokes. If I were to put it into production I would have used the ColumnNames.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources