Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2013
    Posts
    16

    Unanswered: JOINING Columns within the Same Table

    Hey folks,

    What sort of script would convert a pre-existing table into the second below?

    I only want to merge the columns with Primary_IDs 1111 & 3333 to have the same Secondary_ID values, without duplicating any similar Secondary_ID values between the 2 which I've marked in red below.

    Code:
    TABLE A
    ==========
    Primary_ID | Secondary_ID
    1111 | 1
    1111 | 2
    1111 | 3
    1111 | 4
    3333 | 1
    3333 | 2
    3333 | 10
    3333 | 20
    3333 | 100
    3333 | 200
    5555 | 12
    5555 | 34
    7777 | 56
    7777 | 78

    Code:
    NEW TABLE A
    ==========
    Primary_ID | Secondary_ID
    1111 | 1
    1111 | 2
    1111 | 3
    1111 | 4
    1111 | 10
    1111 | 20
    1111 | 100
    1111 | 200
    3333 | 1
    3333 | 2
    3333 | 3
    3333 | 4
    3333 | 10
    3333 | 20
    3333 | 100
    3333 | 200
    5555 | 12
    5555 | 34
    7777 | 56
    7777 | 78

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    insert with not exists subselect?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ????
    I don't understand the requirements....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE
       @a               INT
    ,  @b               INT
    
    SET @a = 1111
    SET @b = 3333
    
    DECLARE @c TABLE (
       Primary_ID       INT
    ,  Secondary_ID     INT
       PRIMARY KEY (Primary_ID, Secondary_ID)
       )
    
    INSERT INTO @c
       (Primary_ID, Secondary_ID) VALUES
       (1111, 1),    (1111, 2),   (1111, 3),   (1111, 4)
    ,  (3333, 1),    (3333, 2),   (3333, 10),  (3333, 20)
    ,  (3333, 100),  (3333, 200), (5555, 12),  (5555, 34)
    ,  (7777, 56),   (7777, 78)
    
    SELECT *
       FROM @c
    
    INSERT INTO @c
       SELECT @a, d.Secondary_ID
          FROM @c AS d
          WHERE  d.Primary_ID = @b
    	     AND NOT EXISTS (SELECT *
    		    FROM @c AS e
    			WHERE  @a = e.Primary_ID
    			   AND d.Secondary_ID = e.Secondary_ID)
    
    SELECT *
       FROM @c
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think your data were too little to show your requirements.


    For example...

    (1) If added the following data into your original data,
    what result(NEW TABLE A) do you want?
    2222 | 1
    2222 | 2
    2222 | 21

    Secondary_ID = (1 , 2) are common in Primary_ID (1111 , 2222 , 3333)


    (2) If added the following data into (1),
    what result(NEW TABLE A) do you want?
    4444 | 2

    Secondary_ID = (1 , 2) are common in Primary_ID (1111 , 2222 , 3333)
    Secondary_ID = (2) are common in Primary_ID (1111 , 2222 , 3333 , 4444)


    (3) If added the following data into (2),
    what result(NEW TABLE A) do you want?
    6666 | 1

    Secondary_ID = (1 , 2) are common in Primary_ID (1111 , 2222 , 3333)
    Secondary_ID = (2) are common in Primary_ID (1111 , 2222 , 3333 , 4444)
    Secondary_ID = (1) are common in Primary_ID (1111 , 2222 , 3333 , 6666)

  6. #6
    Join Date
    Sep 2013
    Posts
    16
    Quote Originally Posted by blindman View Post
    ????
    I don't understand the requirements....
    If Primary_ID column 1111 has adjacent (Secondary_ID) values 1,2,3, and Primary_ID column 3333 has adjacent (Secondary_ID) values 4,5,6, I want a script that merges the 2 to create Primary_ID columns 1111 with adjacent values 1,2,3,4,5,6, and 3333 with adjacent values 1,2,3,4,5,6.

    But if Primary_ID column 1111 has adjacent (Secondary_ID) values 1,2,3, and Primary_ID column 3333 has adjacent (Secondary_ID) values 1,4,5,6, I would not want the new table to have Primary_ID column 1111 with 1,1,2,3,4,5,6 and 3333 with 1,1,2,3,4,5,6.

    @Pat, the I'll check your scripts!

    Quote Originally Posted by tonkuma View Post
    I think your data were too little to show your requirements.


    For example...

    (1) If added the following data into your original data,
    what result(NEW TABLE A) do you want?
    2222 | 1
    2222 | 2
    2222 | 21

    Secondary_ID = (1 , 2) are common in Primary_ID (1111 , 2222 , 3333)


    (2) If added the following data into (1),
    what result(NEW TABLE A) do you want?
    4444 | 2

    Secondary_ID = (1 , 2) are common in Primary_ID (1111 , 2222 , 3333)
    Secondary_ID = (2) are common in Primary_ID (1111 , 2222 , 3333 , 4444)


    (3) If added the following data into (2),
    what result(NEW TABLE A) do you want?
    6666 | 1

    Secondary_ID = (1 , 2) are common in Primary_ID (1111 , 2222 , 3333)
    Secondary_ID = (2) are common in Primary_ID (1111 , 2222 , 3333 , 4444)
    Secondary_ID = (1) are common in Primary_ID (1111 , 2222 , 3333 , 6666)
    I couldn't care less about Primary_ID columns other than the 2 I specified (1111 & 3333). I just want those 2 to have the same values between the 2, and everything else would be the same.

  7. #7
    Join Date
    Sep 2013
    Posts
    16
    This works. Thank you!

    Quote Originally Posted by Pat Phelan View Post
    Code:
    DECLARE
       @a               INT
    ,  @b               INT
    
    SET @a = 1111
    SET @b = 3333
    
    DECLARE @c TABLE (
       Primary_ID       INT
    ,  Secondary_ID     INT
       PRIMARY KEY (Primary_ID, Secondary_ID)
       )
    
    INSERT INTO @c
       (Primary_ID, Secondary_ID) VALUES
       (1111, 1),    (1111, 2),   (1111, 3),   (1111, 4)
    ,  (3333, 1),    (3333, 2),   (3333, 10),  (3333, 20)
    ,  (3333, 100),  (3333, 200), (5555, 12),  (5555, 34)
    ,  (7777, 56),   (7777, 78)
    
    SELECT *
       FROM @c
    
    INSERT INTO @c
       SELECT @a, d.Secondary_ID
          FROM @c AS d
          WHERE  d.Primary_ID = @b
    	     AND NOT EXISTS (SELECT *
    		    FROM @c AS e
    			WHERE  @a = e.Primary_ID
    			   AND d.Secondary_ID = e.Secondary_ID)
    
    SELECT *
       FROM @c
    -PatP

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by keennay View Post
    This works. Thank you!
    Really?

    Were the data marked by Bold, Red inserted?
    Code:
    NEW TABLE A
    ==========
    Primary_ID | Secondary_ID
    1111 | 1
    1111 | 2
    1111 | 3
    1111 | 4
    1111 | 10
    1111 | 20
    1111 | 100
    1111 | 200
    3333 | 1
    3333 | 2
    3333 | 3
    3333 | 4
    3333 | 10
    3333 | 20
    3333 | 100
    3333 | 200
    5555 | 12
    5555 | 34
    7777 | 56
    7777 | 78

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tonkuma, the problem statement that I worked from (and appear to have guessed correctly) was:
    Quote Originally Posted by keennay View Post
    I only want to merge the columns with Primary_IDs 1111 & 3333 to have the same Secondary_ID values, without duplicating any similar Secondary_ID values between the 2 which I've marked in red below.
    This took some work to ferret out, but gave me what I needed to solve the problem specified.

    If I'd had more information (like SQL version), I could have crafted a more elegant solution using MERGE, but I chose to go for the "low hanging fruit" and use the old school syntax that works on almost every SQL version.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Sep 2013
    Posts
    16
    Quote Originally Posted by tonkuma View Post
    Really?

    Were the data marked by Bold, Red inserted?
    I should've specified that Pat's approach helped me understand what I needed to finish off my task.

    In the end I created 2 INSERT INTO statements, the one below and another swapping @a & @b.

    The version of SQL Server I'm testing this on is 2012, but our 'live' database is running off 2008.

    Code:
    ...
    
    SET @a = 1111
    SET @b = 3333
    
    ...
    
    INSERT INTO @c
       SELECT @a, d.Secondary_ID
          FROM @c AS d
          WHERE  d.Primary_ID = @b
    	     AND NOT EXISTS (SELECT *
    		    FROM @c AS e
    			WHERE  @a = e.Primary_ID
    			   AND d.Secondary_ID = e.Secondary_ID)

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In the end I created 2 INSERT INTO statements, the one below and another swapping @a & @b.
    I see.
    That must solved your issue.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to do it by one INSERT statement.
    (Not tested.)

    Example 1:
    Code:
    INSERT INTO @c
    WITH a AS (
    SELECT *
     FROM  @c
     WHERE Primary_ID IN (1111 , 3333)
    )
    SELECT (SELECT TOP (1)
                   Primary_ID
             FROM  a AS e
             WHERE e.Primary_ID <> a.Primary_ID
           )
         , Secondary_ID
     FROM  a
     WHERE NOT EXISTS
           (SELECT *
             FROM  a AS b
             WHERE b.Primary_ID   <> a.Primary_ID
               AND b.Secondary_ID =  a.Secondary_ID
           )
    ;

    Example 2:
    Code:
    INSERT INTO @c
    WITH a AS (
    SELECT *
     FROM  @c
     WHERE Primary_ID IN (1111 , 3333)
    )
    SELECT (SELECT TOP (1)
                   Primary_ID
             FROM  a AS b
             WHERE b.Primary_ID <> MAX(a.Primary_ID)
           )
         , Secondary_ID
     FROM  a
     GROUP BY
           Secondary_ID
     HAVING
           COUNT(*) = 1
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •