Parent Child Link

Question

Post reply

Parent Child Link

David-155102

SSCarpal Tunnel

Points: 4361
More actions
March 7, 2006 at 1:20 am

#167321

Table A contains a column called ID, Table B has two columns ID and PID. The relationship is A.ID = B.PID.However, in Table B it is possible for a PID to also be a ID.
What I want to do is create a query that
a) looks through Table A for a particular ID and then returns all records from Table B where the B.PID is equal to the A.ID
b) for all IDs returned from Table B, also return any IDs where PID is equal to any records in the original result set
E.g. ID in table A = 100, in table B the following IDs have a PID of 100 - 101,102,103. 101 has the following children 101A, 101B, 101C and 102 has a child called 102A. My query would return the following records based upon an initial value of A.ID =1
100,101,102,103,101A,101B,101C,102A.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

I assume you mean an initial value of 100 Try this:

declare @a table(ID varchar(10))

declare @b-2 table(PID varchar(10), ID varchar(10))

insert @a select '100'

insert @b-2 select '100', '101'

insert @b-2 select '100', '102'

insert @b-2 select '100', '103'

insert @b-2 select '101', '101A'

insert @b-2 select '101', '101B'

insert @b-2 select '101', '101C'

insert @b-2 select '102', '102A'

declare @C table (ID varchar(10))

insert @C select '100'

while @@ROWCOUNT > 0

begin

insert @C select b.ID

from @C c inner join @b-2 b on b.PID = c.ID

left join @C c2 on c2.ID = b.ID

where c2.Id is null

end

select * from @C

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 2

Jesper (3/7/2006)
I assume you mean an initial value of 100 Try this:
declare @a table(ID varchar(10))
declare @b-2 table(PID varchar(10), ID varchar(10))
insert @a select '100'
insert @b-2 select '100', '101'
insert @b-2 select '100', '102'
insert @b-2 select '100', '103'
insert @b-2 select '101', '101A'
insert @b-2 select '101', '101B'
insert @b-2 select '101', '101C'
insert @b-2 select '102', '102A'
declare @C table (ID varchar(10))
insert @C select '100'
while @@ROWCOUNT > 0
begin
insert @C select b.ID
from @C c inner join @b-2 b on b.PID = c.ID
left join @C c2 on c2.ID = b.ID
where c2.Id is null
end
select * from @C

Hi,

Is there a way that I could SELECT this data without having to create a temp table for inserts etc? Basically, if you think of the relationships as a tree, one parent could have multiple or no children and each of those children could be a parent in their own right with or without children. As a temporary measure I have nested three select statements as follows:

SELECT MEM_ID

FROM myDB.LINE_X_SET x

JOIN myDB.LINE_SET s on (s.SET_ID = x.SET_ID))

AND UNIT_ID IN (SELECT MEM_ID

FROM myDB.UNIT_TREE (NOLOCK)

WHERE MEM_PID IN

(SELECT MEM_ID

FROM myDB.UNIT_TREE (NOLOCK)

WHERE MEM_PID = x

OR MEM_ID = x)

The problem I have with this solution is that there is no way for me to tell at runtime how many 'levels' to the tree there are. I suppose I could nest even more SELECT statements but that would be just plain stupid! Is there an alternative to this?

Thanks

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 3

Hi,

Does anyone have any suggestions, I'm still no further forward :crazy:

Is there anything new in SQL Server 2005 that can help?

Thanks

David