SQL Server How to join two tables that are exactly the same column names with third table with unique column names to create new view?

edqdpe6u 于 2023-03-11 发布在其他

关注(0)|答案(4)|浏览(108)

I am wanting to create a new view based on joining three tables.

table 1 and table 2 have the same column names but the data in them is representative of different time frames. The third table has columns that are unique in both tables.

My query takes all data of table1 and uses a column in table 1 to extract all records from table 2 that have a match.

and then merge this combined dataset with the third table where ever date is greater than '2017-12-01'

SELECT *
FROM table1
    LEFT JOIN table2
        ON table1.lookup_column = table2.lookup_column
    LEFT JOIN table3
        ON table3.lookup_column = table1.lookup_column
        AND table3.date >= '2017-10-01'

When I run this query to create the new view

select * into new_table 
FROM table1
        LEFT JOIN table2
            ON table1.lookup_column = table2.lookup_column
        LEFT JOIN table3
            ON table3.lookup_column = table1.lookup_column
            AND table3.date >= '2017-10-01'

I get the error :

Column names in each table must be unique. Column name 'abc' in table 'new_table' is specified more than once.

How can I avoid this problem between the two tables where the column names are the exactly the same? or how can I specify that I want to combine table 1 and table 2 on column names?

Edit:

input

table 1                table2                 table
col1    col2           col1    col2           col3    col4
abc     def            ghi     jkl            mno      pqr

Output:

col1   col2   col3   col4
abc    def    mno    pqr
ghi    jkl    mno    pqr

sql-server

来源：https://stackoverflow.com/questions/53815851/how-to-join-two-tables-that-are-exactly-the-same-column-names-with-third-table-w

4条答案

按热度按时间

iyfjxgzm1#

You have to set different names for each column in table1 and table2 that you want to copy in new_table . For example:

select table1.field1 as T1, table2.field1 as T2,
       table1.field2 as T3, table2.field2 as T4
into new_table 
FROM table1
    LEFT JOIN table2
        ON table1.lookup_column = table2.lookup_column
    LEFT JOIN table3
        ON table3.lookup_column = table1.lookup_column
        AND table3.date >= '2017-10-01'

赞(0）回复(0）举报 2023-03-11

xggvc2p62#

Looking at the expected output, and the fact that table1 and table2 are structurally identical, you probably want UNION :

SELECT table1.foo, table1.bar, table3.*
FROM      table1
LEFT JOIN table2 ON table1.lookup_column = table2.lookup_column
LEFT JOIN table3 ON table1.lookup_column = table3.lookup_column AND table3.date >= '2017-10-01'

UNION ALL

SELECT table2.foo, NULL,       table3.*
FROM      table1
LEFT JOIN table2 ON table1.lookup_column = table2.lookup_column
LEFT JOIN table3 ON table1.lookup_column = table3.lookup_column AND table3.date >= '2017-10-01'

赞(0）回复(0）举报 2023-03-11

guz6ccqo3#

Seeing your sample data and output wanted, that doesn't have anything to do with joining 3 tables and left joins.

select * 
from table1
cross join table3
union all
select * 
from table2
cross join table3;

You can see a demo here.

However, your sample data is insufficient to point out expected gotcha. A cross join is a dangerous one if you use without knowing what you are doing.

赞(0）回复(0）举报 2023-03-11

7cjasjjr4#

By select * into [something] you are creating a new table.

You could do this to create a view:

CREATE VIEW [dbo].[ViewName]

AS

SELECT *

FROM table1

LEFT JOIN table2

    ON table1.lookup_column = table2.lookup_column

LEFT JOIN table3

    ON table3.lookup_column = table1.lookup_column

    AND table3.date >= '2017-10-01';

Then you could use the view by:

Select * from [ViewName];

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server How to join two tables that are exactly the same column names with third table with unique column names to create new view?

4条答案

相关问题

热门标签

最新问答