Oracle - how to perform left join on views?

Solution 1:

At least the concert_id column appears in both tables. SELECT * would result in a view having 2 columns with the same name. Resolution: specify the fields explicitly at least for one of the tables

create view concert_event as
    select c.*, e.eventname, ...
    from
        concert_copy c
        left join event_copy e
            on c.concert_id = e.concert_id;

If there are other duplicate names, use aliases. E.g. If both tables have a column name

select c.*, e.name as eventname, ...

Note: if you call SELECT directly, Oracle automatically creates generic names for duplicate columns and expressions, but in views it is required that all column names are visible from the declaration, either because they match the name of a selected column or because an alias is given.

Solution 2:

Don't use asterisk (i.e. SELECT *), but name ALL columns you select, giving each of them distinctive name. For example, if both CONCERT_COPY and EVENT_COPY have column named ID, you'd have to

select c.id concert_id, e.id event_id, ...
from concert_copy c left join event_copy e ...