locked
SQL Query for two tables appending a column from another table RRS feed

  • Question

  • User-1984108393 posted

    Hi,

    Below are two tables which I need result from:

    Table 1

    DocID   DocName   
    1           abc.jpg         

    --------------------------------------------------------

    Table 2

    Dept
    1001
    1005
    1006

    The result that I expect is:

    DocID   DocName   Dept
    1           abc.jpg        1001
    2           abc.jpg        1005
    3           abc.jpg        1006

    As shown above I would like to join the column values of table 2 to rows of table 1.

    I need an SQL Query to achieve this.

    Please help. Urgent help needed, preferably by today.

    Friday, September 7, 2018 12:05 PM

All replies

  • User-2146987983 posted
    CREATE TABLE T1 (DocID INT, DocName VARCHAR(10))
    CREATE TABLE T2 (Dept INT)
    
    INSERT INTO T1 (DocID, DocName) VALUES (1, 'abc.jpg')
    INSERT INTO T2 (Dept) VALUES (1001), (1005), (1006)
    
    SELECT ROW_NUMBER() OVER(ORDER BY B.Dept) AS DocID, A.DocName, B.Dept  FROM T1 A CROSS APPLY T2 B
    
    DROP TABLE T1
    DROP TABLE T2

    Friday, September 7, 2018 12:59 PM
  • User77042963 posted

    Where do these DocID 2, 3 come from? Design your table with relationship through keys.

    Friday, September 7, 2018 1:44 PM