Joel Min Joel Min - 20 days ago 5
SQL Question

Creating a column for a View from two different tables (SQL Server)

I have three tables A, B and C in a SQL Server, B and C share the same foreign key (

AgencyID
) from A, and they also share one same column name:
AgencyName


Table A:
AgencyID
,
AgencyDate
,

Table B:
AgencyID
,
AgencyNumber
, AgencyName

Table C:
AgencyID
,
AgencyInvoiceNumber
, AgencyName

To help you understand the relationship, basically these 3 tables are generated with Entity Framework, where
B
and
C
each extends
A
.

Now I am trying to create one View from these three tables in the following way:

CREATE VIEW [rdo].[Agencies] AS
SELECT
A_1.AgencyID, A_1.AgencyDate,
dbo.B.AgencyNumber, dbo.B.AgencyName,
dbo.C.AgencyInvoiceNumber, dbo.C.AgencyName
FROM dbo.A AS A_1 LEFT OUTER JOIN
dbo.B ON A_1.AgencyID = dbo.B.AgencyID LEFT OUTER JOIN
dbo.C ON A_1.AgencyID = dbo.C.AgencyID


Now, apparently the script errors about
AgencyName
that column name in a view should be unique :( How would I go about this so that both
AgencyName
values from B and C are merged into one view?

Note: I know the example isn't perfect as one could just move the column to table A, but it's just a simplified example, in reality I really cannot simply move the column :(




Reason for having to have duplicated columns in B and C:

Here's the relationships:

A
/ \
A1 A2
/ \
B C


A1 and A2 extends A, B and C extends A1 and A2 respectively.
AgencyName
is an unique attribute that only B and C has. I know a refactoring could be done here to try to get rid of this weird hierarchy, but this legacy project has already grown too big and I cannot afford the cost atm.

Answer

Try using COALESCE to pick the first non null AgencyName:

CREATE VIEW [rdo].[Agencies] AS
SELECT
    A_1.AgencyID, A_1.AgencyDate,
    dbo.B.AgencyNumber, 
    COALESCE(dbo.B.AgencyName, dbo.C.AgencyName) As AgencyName,
    dbo.C.AgencyInvoiceNumber 
FROM dbo.A AS A_1 LEFT OUTER JOIN
        dbo.B ON A_1.AgencyID = dbo.B.AgencyID LEFT OUTER JOIN
        dbo.C ON A_1.AgencyID = dbo.C.AgencyID

You may face the issue where AgencyName is different in B & C in which case you will need a set of rules to follow. My recommendation is to only have AgencyName in one of the tables (Table A?) otherwise you face potential update discrepancies.