Parent-Child + Joins to Same Table - Ouch!

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 2008 Forums

Transact-SQL (2008)

Parent-Child + Joins to Same Table - Ouch!

Author

Topic

Dev
Starting Member

2 Posts

Posted - 2009-09-17 : 08:10:07

Hi there,

I have a problem with joining to the same table to pick up labels. I receive the "The multi-part identifier "child.ItemID" could not be bound." message on the second JOIN clause.

SELECT I1.Name as Parent, I2.Name as Child
FROM Rollups as child, Rollups as parent
JOIN Items as I1 ON parent.ItemID = I1.ItemID
JOIN Items as I2 ON child.ItemID = I2.ItemID
WHERE
child.ItemLeft > parent.ItemLeft AND
child.ItemLeft < parent.ItemRight

Rollups stores hierarchy data leveraging Joe Celko's nested set pattern. Items stores other metadata information. I need to materialize Rollups twice to pickup the parent-child structure I need:

Rollups
ItemID ItemLeft ItemRight

Items
ItemID Name

Thanks for the help!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-17 : 08:19:55

why do you do an IMPLICIT join on Rollups, but EXPLICIT joins to items?

Try following the second link in my sig and providing that information. If we knew what you were trying to get from the database, somebody can most likely give you an optimized query, or fix the one you are trying to use.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Dev
Starting Member

2 Posts

Posted - 2009-09-17 : 08:56:47

Well thanks. Here is some useful information:

CREATE TABLE [dbo].[Rollups](
[RollupID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemLeft] [bigint] NULL,
[ItemRight] [bigint] NULL,

CREATE TABLE [dbo].[Items](
[ItemID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL

Items
1 All Products
2 All Food
3 All Drinks
4 Chicken
5 Nachos
6 Steak
7 Water
8 Beer
9 Juice

Rollups

1 1 1 18
2 2 2 9
3 3 10 17
4 4 3 4
5 5 5 6
6 6 7 8
7 7 11 12
8 8 13 14
9 9 15 16

And what I need to see is:

All Products All Food
All Products All Drink
All Food Chicken
All Food Nachos
All Food Steak
All Drink Beer
All Drink Water
All Drink Juice

Thanks again! I'll get in line with respect to useful posts

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-17 : 11:01:53

Here is a better format for your DDL and some DML


CREATE TABLE #Rollups(
[RollupID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemLeft] [bigint] NULL,
[ItemRight] [bigint] NULL)


CREATE TABLE #Items(
[ItemID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL)

insert into #Items ([Name])
select 'All Products'
union all select 'All Food'
union all select 'All Drinks'
union all select 'Chicken'
union all select 'Nachos'
union all select 'Steak'
union all select 'Water'
union all select 'Beer'
union all select 'Juice'

insert into #Rollups(ItemID,ItemLeft,ItemRight)
select  1 ,1 ,18
union all select 2, 2, 9
union all select 3 ,10, 17
union all select 4 ,3, 4
union all select 5 ,5, 6
union all select 6, 7, 8
union all select 7, 11, 12
union all select 8 ,13, 14
union all select 9 ,15, 16

select * from #items
select * from #Rollups

Now I just have to do a search on Hierarchical data and find a link for you, since i stink at it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-17 : 11:04:28

http://msdn.microsoft.com/en-us/library/ms186243.aspx

I think this is what you want.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources