Joining and Union Issue

Hi,
I have a problem which is hard to describe but I will try. I have table
that has a relationship one to many with another table and it has a
relationship many to many inside it self. Each record had a field that is
called fparent and fparent can relate to the primary field fcomponet. I am
trying to query this table based on pulling all the records based on a join
with the main table. However, inside this resultset there could be more
records to pull if the fparent has more records associated with it. Right
now my query does a 3 level look up (UNION three temp tables together)
however there is a possibility of a 99 level lookup. Is there a way to do a
loop to lookup the multiple level association inside this table?
ThanksJake,
You'd probably get a better response if you posted the DDL (incl PK and FK),
sample data and desired resultset.
See:
http://www.aspfaq.com/etiquette.asp?id=5006
HTH
Jerry
"jake" <jp@.broncos.com> wrote in message
news:%23fByDfA1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a problem which is hard to describe but I will try. I have table
> that has a relationship one to many with another table and it has a
> relationship many to many inside it self. Each record had a field that is
> called fparent and fparent can relate to the primary field fcomponet. I
> am trying to query this table based on pulling all the records based on a
> join with the main table. However, inside this resultset there could be
> more records to pull if the fparent has more records associated with it.
> Right now my query does a 3 level look up (UNION three temp tables
> together) however there is a possibility of a 99 level lookup. Is there a
> way to do a loop to lookup the multiple level association inside this
> table?
> Thanks
>|||Here is the DDL
CREATE TABLE [dbo].[inbm] (
[fcomponent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fcrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fetm] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fparent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fprev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fqty] [numeric](15, 5) NOT NULL ,
[freqd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fltol] [bit] NOT NULL ,
[fnoperno] [numeric](4, 0) NOT NULL ,
[timestamp_column] [timestamp] NULL ,
[identity_column] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Basically, I query this table based WHERE fparent = value and I get a
resultset. In that resultset there could be a fcomponet that is a fparent
for another fcomponent record and so on. I need to be able to get all the
sub levels under when fparent = value.
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uMTmvOB1FHA.1252@.TK2MSFTNGP09.phx.gbl...
> Jake,
> You'd probably get a better response if you posted the DDL (incl PK and
> FK), sample data and desired resultset.
> See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> HTH
> Jerry
> "jake" <jp@.broncos.com> wrote in message
> news:%23fByDfA1FHA.3376@.TK2MSFTNGP14.phx.gbl...
>|||Jake,
See if these help:
Maintaining Hierarchies
http://www.windowsitpro.com/Article.../8826.html?Ad=1
and
Manipulating Hierarchies with UDFs
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=16123&DisplayTab=Article[
/url]
HTH
Jerry
"jake" <jp@.broncos.com> wrote in message
news:OMTCWJC1FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Here is the DDL
> CREATE TABLE [dbo].[inbm] (
> [fcomponent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fcrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fetm] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fparent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fprev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fqty] [numeric](15, 5) NOT NULL ,
> [freqd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fltol] [bit] NOT NULL ,
> [fnoperno] [numeric](4, 0) NOT NULL ,
> [timestamp_column] [timestamp] NULL ,
> [identity_column] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> Basically, I query this table based WHERE fparent = value and I get a
> resultset. In that resultset there could be a fcomponet that is a fparent
> for another fcomponent record and so on. I need to be able to get all the
> sub levels under when fparent = value.
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uMTmvOB1FHA.1252@.TK2MSFTNGP09.phx.gbl...
>