CODEWITHSUNDEEP
sqlsql-servert-sql
edtopia
edtopia

Reputation: 41

Query to join 1 table with 2 overlapped tables

I have 2 tables with identical columns. They have records that are the same and records that are different.

CREATE TABLE APP (
    APPID int NOT NULL,
    APPName varchar(255) NOT NULL,
);

CREATE TABLE HW_SERVER1 (
    APPID int NOT NULL,
    MachineName varchar(255) NOT NULL,
    LocationCd varchar(255),
    CONSTRAINT PK_HW PRIMARY KEY (APPID, MachineName)
);

CREATE TABLE HW_SERVER2 (
    APPID int NOT NULL,
    MachineName varchar(255) NOT NULL,
    LocationCd varchar(255),
    CONSTRAINT PK_HW PRIMARY KEY (APPID, MachineName)
);

HW_SERVER1

APPID MachineName LocationCd
1 m1 a1
1 m2 a2
2 m1 a1
2 m3 a3
3 m5 a2
... ... ...

HW_SERVER2

APPID MachineName LocationCd
1 m1 a1
2 m6 a6
2 m7 a2
2 m5 a2
3 m5 a2
5 m8 a5
6 m2 a2
... ... ...

Each MachineName has a unique LocationCd. What I need is a result set that includes all distinct LocationCd for all APPID.

Result Set

APPID LocationCd
1 a1
1 a2
2 a1
2 a2
2 a3
2 a6
3 a2
5 a5
6 a2

A result set that includes MachineName is useful as well, I just have to remove the duplicate APPID/LocationCd combinations.

Thanks for any feedback or suggestions!

Upvotes: 0

Views: 76

Answers (5)

J.D.
J.D.

Reputation: 1023

Everyone was quick to jump on the easy answer to use UNION to remove dupes (as opposed to UNION ALL) but missed your mention that having MachineName column would be useful to you as well. Please see this answer which gives you the distinct result set with all 3 columns:

WITH CTE_UniqueLocationCd AS
(
    SELECT APPID, LocationCd
    FROM HW_SERVER1

    UNION -- Removes dupes

    SELECT APPID, LocationCd
    FROM HW_SERVER2
),
CTE_UniqueMachineName AS
(
    SELECT APPID, MachineName
    FROM HW_SERVER1

    UNION -- Removes dupes

    SELECT APPID, MachineName
    FROM HW_SERVER2
)

SELECT DISTINCT -- Final removal of dupes that result from the many-to-many join on APPID
    ULC.APPID, UMN.MachineName, ULC.LocationCd
FROM CTE_UniqueLocationCd AS ULC
INNER JOIN CTE_UniqueMachineName AS UMN
    ON ULC.APPID = UMN.APPID

Good question to learn the difference between UNION ALL and UNION, you have my upvote.

Upvotes: 2

David Becker
David Becker

Reputation: 11

You could join the 2 tables and take a distinct set.

SELECT DISTINCT APPID, LocationCd
FROM
(
  SELECT APPID, LocationCd
  FROM HW_SERVER1
  UNION ALL
  SELECT APPID, LocationCd
  FROM HW_SERVER2
)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

A union query would seem to be the most straightforward approach here:

SELECT a.APPID, hw.LocationCd
FROM APP a INNER JOIN HW_SERVER1 hw ON hw.APPID = a.APPID
UNION
SELECT a.APPID, hw.LocationCd
FROM APP a INNER JOIN HW_SERVER2 hw ON hw.APPID = a.APPID;

Note: I perform the joins above to ensure that we only report on apps which appear in the APP table proper.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can use union

select appid, locationcd from HW_SERVER1
union 
select appid, locationcd from HW_SERVER2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

If I understand correctly, you just want union (not union all because you want to remove duplicates):

select APPID, LocationCd
from hw_server1
union  -- on purpose to remove duplicates
select APPID, LocationCd
from hw_server2;

Upvotes: 2

Related Questions