I work as senior DBA with MCSE certifications. I use this space to record code snippets I am using to solve problems during my work or at forums. If you find anything that is useful, please take it and enjoy.

1. yrbyogiogi says:

2. SQLFan says:

Hi Li,

Good code snippets, they are of great use.

It would be appreciated of you if you could contact me(zhanglvlong@gmail.com)

3. Hi Li, One of the students has some T-SQL doubt hence I thought of checking with you.
could you please look into the problem and help with a solution?

The question is as follows

CREATE TABLE People (ID INT PRIMARY KEY , name NVARCHAR(50), motherID INT, fatherID INT, sex NVARCHAR(50));

INSERT People VALUES(1, ‘A’, NULL, NULL, ‘male’);
INSERT People VALUES(2, ‘B’, NULL, NULL, ‘female’);
INSERT People VALUES(3, ‘C’, 1, 2, ‘male’);
INSERT People VALUES(4, ‘X’, NULL, NULL, ‘male’);
INSERT People VALUES(5, ‘Y’, NULL, NULL, ‘female’);
INSERT People VALUES(6, ‘Z’, 5, 4, ‘female’);
INSERT People VALUES(7, ‘T’, NULL, NULL, ‘female’);

CREATE TABLE marriages (
HusbandID INT REFERENCES People(id),
WifeID INT REFERENCES People(id)
)
INSERT marriages VALUES (1,2);
INSERT marriages VALUES (4,5);
INSERT marriages VALUES (1,5);
INSERT marriages VALUES (3,6);

People who are reachable from this person(“A”)
“A” is the reference person who I start.
I want to try reach(get) all of people who have a relationship with this person directly or indirectly.

For example;
When I give ID=1, it should return to me 1,2,3,4,5,6.(order is not important)
Likewise When I give ID=6, it should return to me 1,2,3,4,5,6.(order is not important)

• Jingyang Li says:

A brutal force solution:

```

declare @id int=1

CREATE TABLE People (ID INT PRIMARY KEY , name NVARCHAR(50), motherID INT, fatherID INT, sex NVARCHAR(50));

INSERT People VALUES(1, 'A', NULL, NULL, 'male');
INSERT People VALUES(2, 'B', NULL, NULL, 'female');
INSERT People VALUES(3, 'C', 1, 2, 'male');
INSERT People VALUES(4, 'X', NULL, NULL, 'male');
INSERT People VALUES(5, 'Y', NULL, NULL, 'female');
INSERT People VALUES(6, 'Z', 5, 4, 'female');
INSERT People VALUES(7, 'T', NULL, NULL, 'female');

CREATE TABLE marriages (
HusbandID INT REFERENCES People(id),
WifeID INT REFERENCES People(id)
)
INSERT marriages VALUES (1,2);
INSERT marriages VALUES (4,5);
INSERT marriages VALUES (1,5);
INSERT marriages VALUES (3,6);

;with mycte as (
select ID, null relatedID from People
union
select ID, motherID relatedID from People
union
select ID, fatherID relatedID from People
union
Select HusbandID, WifeId relatedID  from marriages
)

,mycte2 as (
SELECT id  FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
union
SELECT   relatedID FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
)

select id from mycte
where  id in (select id from mycte2 ) or relatedID in (select id from mycte2)
union
select relatedID from mycte
where ( id in (select id from mycte2 )or relatedID in (select id from mycte2))
and relatedID is not null

drop table marriages, People

```

If you don’t mind, could you post your question on MSDN T-SQL forum?

• Hi Li, Thanks for your help. I’ve asked the student and give the reference to the post it in the MSDN T-SQL forum:)
Best Regards,
Prashanth

• Jingyang Li says:
```
DECLARE @Value INT
SET @Value = 6

CREATE TABLE People (ID INT PRIMARY KEY , name NVARCHAR(50), motherID INT, fatherID INT, sex NVARCHAR(50));

INSERT People VALUES(1, 'A', NULL, NULL, 'male');
INSERT People VALUES(2, 'B', NULL, NULL, 'female');
INSERT People VALUES(3, 'C', 1, 2, 'male');
INSERT People VALUES(4, 'X', NULL, NULL, 'male');
INSERT People VALUES(5, 'Y', NULL, NULL, 'female');
INSERT People VALUES(6, 'Z', 5, 4, 'female');
INSERT People VALUES(7, 'T', NULL, NULL, 'female');

CREATE TABLE marriages (
HusbandID INT REFERENCES People(id),
WifeID INT REFERENCES People(id)
)
INSERT marriages VALUES (1,2);
INSERT marriages VALUES (4,5);
INSERT marriages VALUES (1,5);
INSERT marriages VALUES (3,6);

--create source relation dataset
;with dataSource as (
select ID id, null relatedid from People
union
select ID, motherID relatedid from People
union
select ID, fatherID relatedid from People
union
Select HusbandID, WifeId relatedid  from marriages

)

, LeftIDs AS
(
SELECT id, relatedid  FROM dataSource
WHERE relatedid = @Value
UNION ALL
SELECT a.id, a.relatedid FROM dataSource a
JOIN LeftIDs b ON a.id = b.relatedid
)
, RightIDs AS
(
SELECT id, relatedid FROM dataSource
WHERE id = @Value
UNION ALL
SELECT a.id, a.relatedid FROM dataSource a
JOIN LeftIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
, RightIDs2 AS
(
SELECT id, relatedid FROM dataSource
WHERE id = @Value
UNION ALL
SELECT a.id, a.relatedid FROM dataSource a
JOIN RightIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)

)
, RightIDs3 AS
(
SELECT id, relatedid FROM dataSource
WHERE id  = @Value
UNION ALL
SELECT a.id, a.relatedid FROM dataSource a
JOIN RightIDs2 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
, RightIDs4 AS
(
SELECT id, relatedid FROM dataSource
WHERE id  = @Value
UNION ALL
SELECT a.id, a.relatedid FROM dataSource a
JOIN RightIDs3 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)

,finalcte as
(
SELECT id  FROM LeftIDs
UNION
SELECT relatedid  FROM LeftIDs
UNION
SELECT id  FROM RightIDs
UNION
SELECT relatedid
FROM RightIDs
UNION
SELECT id FROM RightIDs2
UNION
SELECT relatedid  FROM RightIDs2
UNION
SELECT id  FROM RightIDs3
UNION
SELECT relatedid  FROM RightIDs3
UNION
SELECT id  FROM RightIDs4
UNION
SELECT relatedid FROM RightIDs4

)

select id from finalcte
WHERE id is not null

drop table marriages, People

--https://stackoverflow.com/questions/18601791/need-query-to-select-direct-and-indirect-customerid-IDses

```
4. zara says:

Hi Li,

Your snippets and solutions are great,