Need help with TSQL syntax and JOIN.

26 Jan 2006 4:34 PM

Wade

Hey all,

I'm having problems with a TSQL query, and I know someone out there can
help.

Note the following:

--
CREATE TABLE #temp
( ID int,
ParentID int,
Text varchar(50)
)

INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')

SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
FROM #temp t1
INNER JOIN #temp t2 ON t1.ID = t2.ParentID
INNER JOIN #temp t3 ON t2.ID = t3.ParentID

DROP TABLE #temp
--

This temp table allows me to define a series of Text values, and change them
together within a Parent-Child relationship. The select returns
"Top:Middle:Bottom", which is exactly what I want.

But, let's pretend that I have 5 more levels. to get the select statement
to work, I'd have to have 5 more inner joins. Let's say I had X more
levels -- how am I to know how many levels to continue to add to my SQL
statement?

Hopefully you can see the problem.

I want to write a SELECT statement that accomplishes the same thing (i.e.
concatenating the TEXT together for X number of parent-child relationships),
but without having to explicitly account for every possible level.

Does anyone know how I can accomplish this?

Thank you very much for your help!

Wade

26 Jan 2006 8:21 PM

Wade

Figured out a way:

--
use tempdb

CREATE TABLE temptbl
( ID int,
ParentID int,
Text varchar(50)
)

GO

CREATE FUNCTION f_categoryPath
( @iID int,
@sText varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @sRetval varchar(8000),
@sSlash char(1),
@t_id int,
@t_parentid int,
@t_text varchar(50)

SET @sSlash = '\'
SET @sRetval = ''

SELECT @t_id = ID,
@t_parentid = ParentID,
@t_text = Text
FROM temptbl
WHERE ID = @iID

set @sRetVal = @sSlash + @t_text + @sText

if @t_parentid is not null
begin
set @sRetval = dbo.f_categoryPath(@t_parentid,@sRetVal)
end

return @sRetval
END

GO

INSERT INTO temptbl (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (3,2,'Bottom1')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (4,3,'Bottom2')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (5,4,'Bottom3')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (6,5,'Bottom4')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (7,6,'Bottom5')

SELECT dbo.f_categoryPath(7,'')

DROP TABLE temptbl
DROP FUNCTION f_categoryPath
--
thanks anyway!

Show quote

"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:epq8QZpIGHA.648@TK2MSFTNGP14.phx.gbl...
> Hey all,
>
> I'm having problems with a TSQL query, and I know someone out there can
> help.
>
> Note the following:
> --
> CREATE TABLE #temp
> ( ID int,
> ParentID int,
> Text varchar(50)
> )
>
> INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
>
> SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
> FROM #temp t1
> INNER JOIN #temp t2 ON t1.ID = t2.ParentID
> INNER JOIN #temp t3 ON t2.ID = t3.ParentID
>
> DROP TABLE #temp
> --
>
> This temp table allows me to define a series of Text values, and change
> them together within a Parent-Child relationship. The select returns
> "Top:Middle:Bottom", which is exactly what I want.
>
> But, let's pretend that I have 5 more levels. to get the select statement
> to work, I'd have to have 5 more inner joins. Let's say I had X more
> levels -- how am I to know how many levels to continue to add to my SQL
> statement?
>
> Hopefully you can see the problem.
>
> I want to write a SELECT statement that accomplishes the same thing (i.e.
> concatenating the TEXT together for X number of parent-child
> relationships), but without having to explicitly account for every
> possible level.
>
> Does anyone know how I can accomplish this?
>
> Thank you very much for your help!
>
> Wade
>

26 Jan 2006 9:26 PM

--CELKO--

Have you gotten a copy of TREES & HIERARCHIES IN SQL yet? There are
several other ways to do this without procedural code at all.