CODEWITHSUNDEEP
mysqlsqlview
Denise Lavezzari
Denise Lavezzari

Reputation: 53

Create view SQL with same columns in two different tables

I have two tables with a common column and I need to create a view with all the attributes of the two tables, but when trying to do that

CREATE VIEW prova AS (
    SELECT * 
    FROM sample s, statistics stat 
    WHERE s.sample_ID = stat.sample_ID

or

CREATE VIEW allData AS (
    SELECT * 
    FROM Sample s 
        INNER JOIN statistics stat vs ON s.sample_ID = stat.sample_ID)

gives error because the column sample_ID is repeated two times. How can I create the view without specificing all the attributes one by one?

Thanks

Upvotes: 0

Views: 1370

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

First, never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

Second, it is usually a best practice to define all the columns for a view explicitly. This can prevent problems down the road.

However, that is not your question. If the only duplicated column is the JOIN key, you can use USING:

CREATE VIEW prova AS
    SELECT *
    FROM sample s JOIN
         statistics stat 
         USING (sample_ID);

If there are other duplicated columns, you will still have a problem. One convenience is to take all columns from one table and list the others:

CREATE VIEW prova AS
    SELECT s.*, stat.col1, stat.col2, . . .
    FROM sample s JOIN
         statistics stat 
         USING (sample_ID);

Upvotes: 3

Related Questions