How to build a view without repeating ids?

Question

I have three tables that I want to join together and create a view as a lookup. The problem is is that I am getting duplicate provider id because the provider_name is different.

Table1

provider_id  provider_name
545          Kent Hospital

Table2

provider_id  provider_name
565          Devin Hospital

table 3

provider_id  provider_name
545          Kent Medical Center

I am getting :

provider_id  provider_name
545          Kent Hospital
545          Kent Medical Center
565          Devin Hospital

final expected result:

provider_id  provider_name
545          Kent Hospital
565          Devin Hospital

Realistically, it doesn't matter which name is used since they are usually almost the same.

But the problem is that I am getting duplicates in provider_id and that's leading into duplicate records on my joins to other tables. I know there are ways to avoid this on the joins to the end-point tables using approaches like this, but to me this seems like a bandage rather than a solution at the core.

my current approach:

--CREATE VIEW lookup_providers AS 
SELECT DISTINCT provider_id, provider_name
FROM table1
UNION
SELECT DISTINCT provider_id, provider_name
FROM table2
UNION
SELECT DISTINCT provider_id, provider_name
FROM table3

Sample tables

CREATE TABLE table1 (provider_id nvarchar(30), provider_name nvarchar(30))
CREATE TABLE table2 (provider_id nvarchar(30), provider_name nvarchar(30))
CREATE TABLE table3 (provider_id nvarchar(30), provider_name nvarchar(30))

INSERT INTO table1 (provider_id, provider_name) VALUES ('545','Kent Hospital')
INSERT INTO table2 (provider_id, provider_name) VALUES ('565','Devin Hospital')
INSERT INTO table3 (provider_id, provider_name) VALUES ('545','Kent Medical Center')

You need to decide what to do when the same provider_id appears in two different tables. There is no DWIM command in sql. — donPablo, Jul 10 '18 at 14:45
not related to the answer, however using DISTINCT and UNION in the same query is just redundand. Leave either UNION (preferable) or DISTINCT statements. — Dmitrij Kultasev, Jul 10 '18 at 15:15

score 2 · Accepted Answer · answered Jul 10 '18 at 14:43

If you don't care about the name, then use GROUP BY:

SELECT provider_id, MIN(provider_name) as provier_name
FROM ((SELECT provider_id, provider_name
       FROM table1
      ) UNION ALL
      (SELECT provider_id, provider_name
       FROM table2
      ) UNION ALL
      (SELECT provider_id, provider_name
       FROM table3
      )
     ) tt
GROUP BY provider_id;

There are more elaborate mechanisms if you want to choose a particular name -- the longest, the shortest, prioritizes by a table. But you specify that an arbitrary name is fine, so MIN() should do what you want.

score 1 · Answer 2 · answered Jul 10 '18 at 14:45

A slight guess, but i'd assume you're after something like:

CREATE VIEW lookup_providers AS

    WITH CTE AS(
        SELECT provider_id, provider_name
        FROM table1
        UNION
        SELECT provider_id, provider_name
        FROM table2
        UNION
        SELECT provider_id, provider_name
        FROM table3),
    RNs AS(
        SELECT provider_id, provider_name,
               ROW_NUMBER() OVER (PARTITION BY provider_id ORDER BY provider_name) AS RN
        FROM CTE)
    SELECT provider_id, provider_name
    FROM RNs
    WHERE RN = 1;

GO

How to build a view without repeating ids?

2 Answers2