Using coalesce to create a view

Question

I am creating a view with below script from 3 tables. I am having problems when a HCPCS is found in MUES_OUT_2010_07 and MUES_DME_2010_07 and not in MUES_PHY_2010_07

CREATE VIEW MUES AS
SELECT COALESCE(TPHY.HCPCS, TOUT.HCPCS, TDME.HCPCS) AS HCPCS
, COALESCE(UNITS_PHY, -1) AS UNITS_PHY
, COALESCE(UNITS_OUT, -1) AS UNITS_OUT
, COALESCE(UNITS_DME, -1) AS UNITS_DME
FROM MUES_PHY_2010_07 TPHY
FULL OUTER JOIN MUES_OUT_2010_07 TOUT
ON TPHY.HCPCS = TOUT.HCPCS
FULL OUTER JOIN MUES_DME_2010_07 TDME
ON TPHY.HCPCS = TDME.HCPCS
GO

My tables are created like below

CREATE TABLE [dbo].[MUES_OUT_2010_07](
    [HCPCS] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [UNITS_OUT] [smallint] NULL,
    CONSTRAINT [PK_MUES_OUT_2010_07] PRIMARY KEY CLUSTERED 
(
    [HCPCS] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].[MUES_DME_2010_07](
        [HCPCS] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [UNITS_DME] [smallint] NULL,
        CONSTRAINT [PK_MUES_DME_2010_07] PRIMARY KEY CLUSTERED 
    (
        [HCPCS] ASC
    )WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO

CREATE TABLE [dbo].[MUES_PHY_2010_07](
            [HCPCS] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
            [UNITS_PHY] [smallint] NULL,
            CONSTRAINT [PK_MUES_DME_2010_07] PRIMARY KEY CLUSTERED 
        (
            [HCPCS] ASC
        )WITH FILLFACTOR = 90 ON [PRIMARY]
        ) ON [PRIMARY]
        GO

insert into MUES_OUT_2010_07 select 'E1036', 1
insert into MUES_DME_2010_07 select 'E1036', 1

i expect the view to create a one record like below since HCPCS 'E1036' is not in the MUES_PHY_2010_07 table

HCPCS UNITS_PHY UNITS_OUT UNITS_DME

E1036 -1 1 1

but it creates two records like below

E1036 -1 -1 1

E1036 -1 1 -1

what am i doing wrong in the view

Answer 1

You are using a full outer join. That means that you have to be careful, because a value could be missing from any table. In particular, when you get to the third table, the on conditions need to refer to both the previous tables, in case one or the other is missing.

So, this might fix your problem:

CREATE VIEW MUES AS
    SELECT COALESCE(TPHY.HCPCS, TOUT.HCPCS, TDME.HCPCS) AS HCPCS,
           COALESCE(UNITS_PHY, -1) AS UNITS_PHY,
           COALESCE(UNITS_OUT, -1) AS UNITS_OUT,
           COALESCE(UNITS_DME, -1) AS UNITS_DME
    FROM MUES_PHY_2010_07 TPHY FULL OUTER JOIN
         MUES_OUT_2010_07 TOUT
         ON TPHY.HCPCS = TOUT.HCPCS FULL OUTER JOIN
         MUES_DME_2010_07 TDME
         ON COALESCE(TPHY.HCPCS, TOUT.HCPCS) = TDME.HCPCS;