SQL Join across multiple tables and order by single column

4k views Asked by At

I am trying to join multiple tables and order the data based on a common column that they share. Here is an example:

Table A

    pk  itemId  text
    1   10      ordered
    1   5       make
    1   8       it
    1   4       not

Table B

    pk  itemId  text    variable
    1   6       sense   94
    1   1       this    99
    1   2       text    98

Table C

    pk  itemId  text    anotherVariable
    1   3       does    97
    1   7       unless  93
    1   9       is      91

I need the final table to look like this:

Result Table

    pk  itemId  text    variable
    1   1       this    99
    1   2       text    98
    1   3       does    97
    1   4       not     NULL
    1   5       make    NULL
    1   6       sense   94
    1   7       unless  93
    1   8       it      NULL
    1   9       is      91
    1   10      ordered NULL

I am using the following query but it is not working...

    SELECT *
    FROM tableA as A
    INNER JOIN tableB as B ON A.pk = B.pk
    INNER JOIN tableC as C ON A.pk = C.pk
    ORDER BY A.itemId, B.itemId, C.itemId

Edit: added another variable to further explain my problem, across the tables I do not always have the same column names on all tables. On other tables there may be different columns that I want included in the result table.

1

There are 1 answers

2
Felix Pamittan On BEST ANSWER

Use UNION ALL instead.

SELECT pk, itemId, [text], variable = NULL FROM TableA UNION ALL
SELECT pk, itemId, [text], variable FROM TableB UNION ALL
SELECT pk, itemId, [text], anotherVariable FROM TableC
ORDER BY pk, itemId

As long as you have the same number of columns in all of the SELECT statements, and all columns have the same data type, UNION ALL should work.

Here is the documentation for UNION.