CODEWITHSUNDEEP
mysqlsql
Ratha
Ratha

Reputation: 9692

How to avoid duplicate columns when doing left join?

I have two tables. Each has number of columns. But both has two columns in common.(ie: SiteCode,companyId)

I try to do left join for both common columns to create a view.

CREATE VIEW [dbo].[vwAlarmActionSummaryYFUserSite]
AS
SELECT  *
FROM        [dbo].[vwAlarmActionSummary] alarmaction
LEFT JOIN   [dbo].[vwYfUserToSiteMappings] usersite on alarmaction.SiteCode = usersite.SiteCode 
and alarmaction.CompanyId = usersite.CompanyId

But Im getting 

Column names in each view or function must be unique. Column name 'SiteCode'on 'vwAlarmActionSummaryYFUserSite' is specified more than once.

How can I avoid this? My requirement is get all columns from those two tables based on the above two conditions

Upvotes: 1

Views: 6287

Answers (2)

Xingzhou Liu
Xingzhou Liu

Reputation: 1559

It is very bad practice to use * in definitions view generally. There is no way to specifically exclude a column from one of the joined tables. However, you can use * (all columns) for one table, while providing explicit column names for the other, as below:

CREATE VIEW [dbo].[vwAlarmActionSummaryYFUserSite]
AS
SELECT  
    alarmaction.[column name],
    ...
    usersite.*
    FROM        [dbo].[vwAlarmActionSummary] alarmaction
    LEFT JOIN   [dbo].[vwYfUserToSiteMappings] usersite on 
    alarmaction.SiteCode = usersite.SiteCode 
    and alarmaction.CompanyId = usersite.CompanyId

Best practice is still to explicitly list all columns from both tables, and include 'SiteCode' only once.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

Don't select *. Pick your column names.

Upvotes: 0

Related Questions