About

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 usefule, please take it and enjoy.

Advertisements

6 Comments on “About”

  1. yrbyogiogi says:

    waiting for your code snippets to learn more & more.

  2. SQLFan says:

    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)

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

    • 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
        
        
        
         

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s