About

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.

I may respond to your questions about my posting here.

8 Comments on “About”

  1. yrbyogiogi says:
    July 21, 2011 at 6:51 am

    waiting for your code snippets to learn more & more.

    Reply
  2. SQLFan says:
    December 1, 2015 at 7:42 pm

    Hi Li,

    Good code snippets, they are of great use.

    As a SQL Server fan, may I have your email address?

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

    Reply
  3. Prashanth Jayaram says:
    March 7, 2018 at 3:04 pm

    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)
    Likewise When I give ID=7, it should return to me 7.

    Reply
    • Jingyang Li says:
      March 7, 2018 at 9:10 pm

      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?

      Reply
      • Prashanth Jayaram says:
        March 8, 2018 at 9:05 am

        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:
        March 9, 2018 at 2:06 pm 
          
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:
    October 17, 2019 at 1:43 pm

    Hi Li,

    Your snippets and solutions are great,
    As a SQL Server beginner, may I have your email address?
    It would be appreciated if you could contact me.
    I need your help more in the SQL Script.

    Reply

Leave a reply to Jingyang Li Cancel reply