Select Query

[Somu79]

Hi,

I need to built a query for the following table [ex: tblMater].

MemberId Parentid

B A
C B
D C
F D
G F
H G
I B
Y Z
T P


In the where clause i will give only the value 'B' [either for memberid or parentid], the query should return the following values

B A
C B
D C
F D
G F
H G
I B

It's very urgent, kindly help to built a query.

Thaks & Regards
-Somu

[Taofiq]

Hi,

I need to built a query for the following table [ex: tblMater].

MemberId Parentid

B A
C B
D C
F D
G F
H G
I B
Y Z
T P


In the where clause i will give only the value 'B' [either for memberid or parentid], the query should return the following values

B A
C B
D C
F D
G F
H G
I B

It's very urgent, kindly help to built a query.

Thaks & Regards
-Somu

I don't think it's posible to have the output you stated based on criteria you want your query to have with the records on your table. anyways the query below consider the criteria you specified but with this output
b a
c b
i b

rather what your specified output

SELECT tblMater.MemberId, tblMater.Parentid
FROM tblMater
WHERE tblMater.MemberId="B" OR tblMater.Parentid="B"

[Somu79]

Taofiq

Thank you.

I want all the records which are related to 'B'

For ex:
B A
C B
D C

in above data "D C" "C" have the relation with "B" in above row. Like wise i want the output.

I hope i have clearly explained my requirement.

kindly help me.

-Somu

[mikr0s]

Check if your Database System supports recursive queries.

--HTH--

[Somu79]

mikr0s

I am using SQL Server 2000. I don't know whether it supports the recursive query. I searched in web, but it seems to be it won't support.

Advice pls, how to proceed.

-Somu.

[mikr0s]

you could use a function that returns all the values related to a selected value:

CREATE FUNCTION my_recursion (@field NVARCHAR(5))
returns @mytbl table (ident INT IDENTITY, fld NVARCHAR(5))
BEGIN
--get memberid values having @field as memberid or parentid
INSERT INTO @mytbl(fld)
SELECT DISTINCT MemberId
FROM tblMater
WHERE MemberId = @field OR Parentid= @field

--get parentid values having @field as memberid or parentid
INSERT INTO @mytbl(fld)
SELECT DISTINCT a.Parentid
FROM tblMater a LEFT JOIN @mytbl tbl ON a.Parentid = tbl.fld
WHERE (a.MemberId = @field OR a.Parentid = @field)
AND tbl.fld IS NULL

DECLARE @runner INT

--inititalize runner
SET @runner = 1

WHILE @runner <= (SELECT ISNULL(MAX(ident), 0) FROM @mytbl)
BEGIN
--initialize @field on each pass
SET @field = NULL

--get current value for @field
SELECT @field = fld
FROM @mytbl
WHERE ident = @runner

--get memberid values related to @field
INSERT INTO @mytbl(fld)
SELECT DISTINCT a.MemberId
FROM tblMater a LEFT JOIN @mytbl tbl ON a.MemberId = tbl.fld
WHERE (a.MemberId = @field OR a.Parentid = @field)
AND tbl.fld IS NULL

--get parentid values related to @field
INSERT INTO @mytbl(fld)
SELECT DISTINCT a.Parentid
FROM tblMater a LEFT JOIN @mytbl tbl ON a.Parentid = tbl.fld
WHERE (a.MemberId = @field OR a.Parentid = @field)
AND tbl.fld IS NULL

SELECT @runner = @runner + 1
END


RETURN

END

GO

--in order to get your values you could use one of the following:
SELECT tblMater.MemberId, tblMater.Parentid
FROM tblMater
WHERE tblMater.MemberId IN (SELECT fld FROM dbo.my_recursion('B'))
OR tblMater.Parentid IN (SELECT fld FROM dbo.my_recursion('B'))

--or
SELECT tblMater.MemberId, tblMater.Parentid
FROM tblMater JOIN dbo.my_recusrion('B') rec ON tblMater.Memberid = rec.fld
UNION
SELECT tblMater.MemberId, tblMater.Parentid
FROM tblMater JOIN dbo.my_recusrion('B') rec ON tblMater.Parentid = rec.fld

--or
any join statement of your convenience

--HTH--

[Somu79]

mikr0s

Thank you very much. I will check it and get back to u.

-Somu.