Search code examples
sql-view

How to create a SQL view from different tables with same column names?

trying to create a view from few tables, when I ran the query I get an error: column xxx duplicated. well, it's true. I assumed that it will be represented as tbl1.xxx, tbl2.xxx but apparently this is represented as xxx, xxx and thats why I get the error. i have to say that it's a lot of columns so I don't want to name all the columns.

Solution

  • Aliasing the duplicate column names to something distinct is probably your best option here:

    CREATE VIEW yourView AS (
    SELECT t1.xxx AS xxx_1, t2.xxx AS xxx_2, ...
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.id = t2.t1_id
)

    The only other option which comes to mind, as you have suggested, would be to formally rename the similar columns in one/both of the tables. But, this is draconian and you should not have to do such plumbing for something as simple as creating a view.