Hierachy and joins - 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)

Hierachy and joins

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-07-25 : 06:17:51

hello All,
am a bit confused here.

I have 3tables as thus

1. Categories [PK]
2. Sub categories [FK one to many PK Categories]
3. products [FK one to many PK Sub categories]

now,

I want to select all products from the products table that fall under the categories, electronics.

But the is no relationship between 1 and 3

How do i do this.

Thanks
Afrika

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-25 : 06:24:13

How about this..

Select p.* 
from Product p Join SubCategory s on p.SubCategoryID = s.SubCategoryID
Join Category c on s.CategoryID = c.CategoryID
Where c.CategoryName = 'Electronics'

Select p.* 
from Product p where p.SubCategoryID in (Select SubCategoryID from SubCategory where CategoryID in (Select CategoryID from Category where CategoryName = 'Electronics'))

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 06:38:05

Probably no help

but we do our categories & sub-categories (and the great-great-grand-child-sub-categories!) as a self referencing table

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-07-25 : 18:20:57

thanks for the advice Harsh Athalye,
how do you mean Kristen ?

lets learn something new again.

am still working on the solution above
Afrika

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-25 : 18:57:49

quote:
Originally posted by afrika

thanks for the advice Harsh Athalye,
how do you mean Kristen ?

lets learn something new again.

am still working on the solution above
Afrika

I think Kristen means he self-joins to the same table to derive all the categories/sub-categories/sub-sub-categories etc. It would be interesting to see, because I rarely use self joins. To me (using self-joins) usually involves thinking about the problem slightly differently, and sometimes I get stuck with the same solutions over and over and miss the opportunity to do something different/better/cooler/whatever when I have the chance.

anyway, it looks like HA has the solution for you.

-ec

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-07-25 : 18:58:13

Finally got it to work, thanks a lot Harsh

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-07-25 : 19:00:21

hi Eyechart,
just saw your post. After posting the above.

Yes it works, thanks for the advice. Guess i have loads of reading to do tonight

Afrika

Kristen
Test

22859 Posts

Posted - 2006-07-26 : 07:17:17

"It would be interesting to see, because I rarely use self joins"

Our ecommerce shopping cart application is used by a number of customers, and is intended to be as "generic" as possible.

How can I put this politely ... "The customers vary greatly in the number of drill-down levels they think their customers will be bothered to navigate"

So we need a flexible solution. Basically we need to display an indented tree of categories, and a breadcrumb of the various parents to the current category being viewed.

We have a Category Code column, and a Parent column. We probably should have a Path column too, but we don't (we limit the number of levels to 6, and its easier to have 6 outer joins rather than looping to get all the parents) [Actually we could probably use a Tally table to "split" a Path set-based rather than looping, now I come to think of it ...]

That's about it really ... unless I haven't answered the question that was on your mind, in which case please ask.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources