locked
Joining two tables together RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Just wondering how can I join two table together using the ID column, without causing any duplication.

    Table 1

    id          time                                          status              desc     space
    81038   2013-09-26 23:57:05.000           cancelled           XRt      1         
    81038   2013-09-27 00:03:22.000           completed         USn      1
    81038   2013-09-27 13:12:52.000           cancelled           Livr      1         
    81038   2013-09-29 16:36:56.000           completed         CTd      1
    81038   2013-09-30 04:06:17.000           completed         XRt       1
    81038   2013-10-02 11:58:44.000           completed         XRt       1
    81038   2013-10-05 18:31:08.000           completed         XRt       1

    Table 2

    Id         desc      datetime                        space   
    81038   USn      Sep 27 2013  9:23AM     1         
    81038   CTd      Sep 29 2013  7:31PM     1         
    81038   XRt       Sep 30 2013  4:08AM     1         

    I want to join the above TWO tables into ONE so they can be displayed on Reporting Services, in a single table.

    id          time                                          status              desc     space    Id         desc      datetime                      space   
    81038   2013-09-26 23:57:05.000           cancelled           XRt      1          81038   USn      Sep 27 2013  9:23AM     1
    81038   2013-09-27 00:03:22.000           completed         USn      1          81038   CTd      Sep 29 2013  7:31PM     1
    81038   2013-09-27 13:12:52.000           cancelled           Livr      1          81038   XRt       Sep 30 2013  4:08AM     1
    81038   2013-09-29 16:36:56.000           completed         CTd      1          NULL    NULL    NULL                            NULL
    81038   2013-09-30 04:06:17.000           completed         XRt       1          NULL    NULL    NULL                            NULL
    81038   2013-10-02 11:58:44.000           completed         XRt       1          NULL    NULL    NULL                            NULL
    81038   2013-10-05 18:31:08.000           completed         XRt       1          NULL    NULL    NULL                            NULL

    Thursday, October 10, 2013 8:39 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote 
    SELECT *
(SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY [time]) AS Seq,* FROM Table1)m
LEFT JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY [datetime]) AS Seq,* FROM Table2)n
ON n.id = m.id
AND n.Seq = m.Seq

    • Proposed as answer by Harsh Kumar Thursday, October 10, 2013 9:18 AM
    • Marked as answer by Sam233 Thursday, October 10, 2013 10:27 AM
    Thursday, October 10, 2013 9:03 AM