CODEWITHSUNDEEP
sqlsql-servert-sqlview
Gray Meiring
Gray Meiring

Reputation: 431

SQL Change View Name / Joins

I am trying to join two views I created, however I am joining them using their common field (cAuditNumber).

The issue is, once I have done the joins, it will not let me create the view as it cannot have the field name cAuditNumber twice.

Is the cAuditNumber the PK I should use?

How do I correct this and still join the tables?

CREATE VIEW KFF_Sales_Data_Updated AS
SELECT CustSalesUpdated.*, StkSalesUpdated.*
FROM CustSalesUpdated
INNER JOIN StkSalesUpdated
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

I get the following error:

Msg 4506, Level 16, State 1, Procedure KFF_Sales_Data_Updated, Line 2 Column names in each view or function must be unique. Column name 'cAuditNumber' in view or function 'KFF_Sales_Data_Updated' is specified more than once.

Upvotes: 2

Views: 1305

Answers (3)

FutbolFan
FutbolFan

Reputation: 13723

Using select * is a bad practice in general. On the other hand, it is a good practice to alias your table names and columns. Especially in your case, your table names as well as your same columns name(across two tables) could use aliases. The database is confused as to which cAuditNumber is coming from where. So, alias comes in handy. 

CREATE VIEW KFF_Sales_Data_Updated
AS
SELECT 
     csu.cAuditNumber
    ,csu.Col1
    ,csu.Col2
    ,csu.Col3
    ,ssu.Col1 AS StkCol1
    ,ssu.Col2 AS StkCol2
    ,ssu.Col3 AS StkCol3
FROM CustSalesUpdated csu
INNER JOIN StkSalesUpdated ssu ON csu.cAuditNumber = ssu.cAuditNumber

Upvotes: 0

Mihaela Neagu
Mihaela Neagu

Reputation: 1

CREATE VIEW KFF_Sales_Data_Updated AS
SELECT csu.cAuditNumber cAuditNumber1 , ssu.cAuditNumber cAuditNumber2  
FROM CustSalesUpdated csu
INNER JOIN StkSalesUpdated ssu
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

You could add any other column in the select statement from the two tables but if there are two column with the same name you should give them aliases

Upvotes: 0

MartianCodeHound
MartianCodeHound

Reputation: 380

Substitute your own column names instead of ColumnA, Column B, etc, but it should follow this format:

CREATE VIEW KFF_Sales_Data_Updated AS
SELECT CustSalesUpdated.cAuditNumber
    ,CustSalesUpdated.ColumnA
    ,CustSalesUpdated.ColumnB
    ,CustSalesUpdated.ColumnC
    ,StkSalesUpdated.ColumnA as StkColumnA
    ,StkSalesUpdated.ColumnB as StkColumnB
    ,StkSalesUpdated.ColumnC as StkColumnC
FROM CustSalesUpdated
INNER JOIN StkSalesUpdated
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

You only have to alias duplicate columns using "as", or you can use it to rename any column that you so desire.

Upvotes: 1

Related Questions