Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2008
    Posts
    5

    Unanswered: Associate two fields with no relationship

    table structure:
    TABLE_A
    - id
    - a_serial_number
    - sequence_number

    TABLE_B
    - id
    - b_serial_number
    - sequence_number

    TABLE_C
    - id
    - a_serial_number
    - b_serial_number

    I need a query to populate TABLE_C with the objective to pair a and b serial numbers only based on an ordering of the respective sequence numbers.

    I get partially to my solution by using this statement:
    Insert into TABLE_C(a_serial_number)
    select a_serial_number from TABLE_A order by sequence_number

    I am an amateur at at SQL and have been unable to narrow my search terms due to lack of understanding of the terminology.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    does TABLE_Asequence_number = b.seqience_number?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2008
    Posts
    5
    the sequence numbers of each table may not equal each other. Their sole purpose is to order the serial numbers in their own table.

    That is part of my problem. I can't and don't want to do a join.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am still fuzzy on what you want. what does the desired result look like in comparison to some sample data. show both.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Oct 2008
    Posts
    5
    Table_a
    1, 11111, 345
    2, 11112, 235
    3, 11113, 312

    Table_b
    1, 1112, -468
    2, 1113, 123
    3, 1114, 101

    Tabel_c
    1, 11112, 1112
    2, 11113, 1114
    3, 11111, 1113

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    so table C is supposed to match the highest sequence numbers in both tables and then the next record is supposed to be the 2nd highest sequence numbers etc...
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Oct 2008
    Posts
    5
    Yes.

    Thank you for stating that clearly.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I sit in requirements meetings and make the illusion of order from chaos for a living.

    2 assumptions here. 1. you are using SQL 2005 2.the ID column in table c is an identity.

    Code:
    SELECT ROW_NUMBER() OVER (ORDER BY sequence_number) as row,
    a_serial_number
    into #A
    FROM Table_a
    
    SELECT ROW_NUMBER() OVER (ORDER BY sequence_number) as row,
    b_serial_number
    into #B
    FROM Table_a
    
    INSERT INTO Tabel_c(a_serial_number,b_serial_number) 
    SELECT #A.a_serial_number,#B.b_serial_number
    FROM #A
    JOIN #B
    ON #A.row = #B.row
    Last edited by Thrasymachus; 10-23-08 at 15:01.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #Table_A (
       id		     INT
    ,  serial_number     INT
    ,  sequence_number   INT      NOT NULL
       CONSTRAINT XPKTable_A
          PRIMARY KEY (sequence_number)
       )
    
    CREATE TABLE #Table_B (
       id		     INT
    ,  serial_number     INT
    ,  sequence_number   INT      NOT NULL
       CONSTRAINT XPKTable_B
          PRIMARY KEY (sequence_number)
       )
    
    INSERT INTO #Table_A (
       id, serial_number, sequence_number
       )  SELECT 1, 11111, 345
    UNION SELECT 2, 11112, 235
    UNION SELECT 3, 11113, 312
    
    INSERT INTO #Table_B (
       id, serial_number, sequence_number
       )  SELECT 1, 1112, -468
    UNION SELECT 2, 1113, 123
    UNION SELECT 3, 1114, 101
    
    SELECT * FROM #Table_A
    SELECT * FROM #Table_B
    
    SELECT b.PatP, a.serial_number, b.serial_number
       FROM (SELECT *, (SELECT Count(*) FROM #Table_A AS z
       WHERE z.sequence_number <= y.sequence_number) AS r937
       FROM #Table_A AS y) AS a FULL JOIN (SELECT *
    ,  (SELECT Count(*) FROM #Table_B AS y
       WHERE y.sequence_number <= x.sequence_number) AS PatP
       FROM #Table_B AS x) AS b ON (b.PatP = a.r937)
    -PatP

  10. #10
    Join Date
    Oct 2008
    Posts
    5
    So the concept is to use two temporary tables for holding the sequenced serial numbers and then join these two and insert into the the final destination.

    I get an "error converting data type ..." but with a few data type tweaks, This should work.

    Thank you.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My solution does not use temp tables, and has worked with every version of SQL Server that Microsoft has ever marketed.

    -PatP

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    dont feel bad Pat. I like yours. Mine is just more readable.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Feb 2007
    Posts
    62
    Quote Originally Posted by Thrasymachus
    I sit in requirements meetings and make the illusion of order from chaos for a living.

    2 assumptions here. 1. you are using SQL 2005 2.the ID column in table c is an identity.

    Code:
    SELECT ROW_NUMBER() OVER (ORDER BY sequence_number) as row,
    a_serial_number
    into #A
    FROM Table_a
    
    SELECT ROW_NUMBER() OVER (ORDER BY sequence_number) as row,
    b_serial_number
    into #B
    FROM Table_a
    
    INSERT INTO Tabel_c(a_serial_number,b_serial_number) 
    SELECT #A.a_serial_number,#B.b_serial_number
    FROM #A
    JOIN #B
    ON #A.row = #B.row
    Can you not just make inline views instead of nasty temp tables? (I can't test right now so there might be a good reason)


    Code:
    
    INSERT INTO Tabel_c(a_serial_number,b_serial_number) 
    SELECT A.a_serial_number,B.b_serial_number
    FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY sequence_number) as row,
    a_serial_number
    FROM Table_a
    ) as A
    inner join
    (
    SELECT ROW_NUMBER() OVER (ORDER BY sequence_number) as row,
    b_serial_number
    FROM Table_b
    )
    on A.row = B.row

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the concept is the same. you can do it lots of ways. you can use the identity function, table variables, views, you could do bubble sorts and use cursors. whatever.

    why does everyone hate temp tables? I admit I use them more when I am being intellectually lazy.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    why does everyone hate temp tables?
    because of the overhead

    derived tables a.k.a. inline views a.k.a. subqueries are mucho mejor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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