Basic SQL statement question for multiple tables

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)

Basic SQL statement question for multiple tables

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2006-11-04 : 19:00:24

Hi all!

I am having a hard time getting my head around this one - any help would be greatly appreciated.

I have two tables that 'loop' back on each other. There is one table called PRODUCTS and another table called OPTIONS.

PRODUCTS has attributes of ProductID, Manufactuer, Name, and Partno.
OPTIONS has attributes of ProductID, Partno, OptionID.

Each product in the PRODUCTS table will have many options in the OPTIONS table that can be used with that particular product.

Now heres the tricky bit. The ProductID in the OPTIONS table is related to the ProductID in the PRODUCTS table. So when you look at the ProductID for a product in the PRODUCTS table, you can see all the options for it in the OPTIONS table. But you can only see the Partno for that option in the OPTIONS table. So in order to find out more information about the optional item, the OptionID attribute in the OPTIONS table relates back to the ProductID attribute in the PRODUCTS table. So the idea here is that you match up the OPTIONS.OptionID back to the PRODUCTS.ProductID and should be able to get the Manufacturer, Name, etc from the

Therefore PRODUCTS.ProductID is realted to OPTIONS.ProductID as well as OPTIONS.OptionID.

I am finding it hard to get the data I want through just one SQL SELECT statement. Do I need to look into nested SELECT statements or something like that to get the data correctly?

I don't need any SQL statement solutions. I would just like to know what area I should look in to get the correct SQL statment e.g. nested SELECT statements.

Thanks for taking the time to read this, I am very thankful for any help you might be able to give me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-04 : 21:45:41

[code]
select *
from PRODUCTS p
INNER JOIN OPTINOS o ON p.ProductID = o.ProductID
INNER JOIN PRODUCTS r ON o.OptionID = r.ProductID
[/code]

token
Posting Yak Master

133 Posts

Posted - 2006-11-05 : 14:54:12

Here is how my SQL looks now:

---
SELECT dbo.Options.Partno as OP
FROM dbo.Options, dbo.Products, dbo.Stage
WHERE (dbo.Stage.ProductID = #URL.ProductID#)
AND (dbo.Products.Partno = dbo.Stage.Partno)
AND (dbo.Options.ProductID = dbo.Products.ProductID)
---

From this query, I get a set of results under the header "OP" that lists all the options records that relate to the Product selected. However it is only returning me the Partno as I asked to.

I now need to reference the PRODUCTS.Partno table using the OPTIONS.Partno to find out what the name of the part is. The name is located in PRODUCTS.Model

How do I get this info out of the PRODUCTS table? I treat the above SQL statement as a view and then interogate that data because I am doing the SQL on a ColdFusion/ASP page that retrieves records from the database.

Any suggestions?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-05 : 17:05:01

add the column to the select list ?


SELECT dbo.Options.Partno as OP, dbo.PRODUCTS.Model

token
Posting Yak Master

133 Posts

Posted - 2006-11-05 : 17:51:53

I tried that, but it returns me the Options.Partno correctly but the Products.Model returns the same model name over and over again. Also the model name it returns does not correspond to the Options.Partno, it only returns the model name of the Product that is being viewed.

So to elaborate... I have gone to a webpage and clicked to view a HP Image Scanner. The URL for this is something like mypage.com?ProductID=2

Now when I run the SQL statement above (with dbo.Products.Model in the SELECT statement) I get this:

OP         | Model
=======|=================
L1996A     HP Image Scanner
L1963P     HP Image Scanner

Clearly that is not the correct model name for the two options listed.

I just cant understand how to get the model name correctly of each option :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-05 : 20:27:10

try this


SELECT     o.Partno as OP, m.Model as Model
FROM       dbo.Options o
INNER JOIN dbo.Products p   ON o.ProductID = p.ProductID
INNER JOIN dbo.Stage s      ON s.Partno    = p.Partno
INNER JOIN dbo.Products m   ON o.OptionID  = m.ProductID
WHERE      s.ProductID = #URL.ProductID#

token
Posting Yak Master

133 Posts

Posted - 2006-11-06 : 12:06:14

Oh my god! Thank you so much! It worked for me khtan.

Do you have a PayPal account so that I can give you a little gift? E-mail me your details if you can.

Thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources