CODEWITHSUNDEEP
sqlsql-server
Yana
Yana

Reputation: 975

Compare columns but exclude specific pairs

I want to write query to compare columns between two tables but to exclude specific pairs which point to the same thing but are written differently.

ID Name
1 NYSE
2 OTC
3 OTC
ID Name
1 New York Stock Exchange
2 NASDAQ
3 Over the Counter

This means that I when I compare columns to see which pairs are different the only result that I will have is ID 2 OTC - NASDAQ. The rest should be excluded because NYSE = New York Stock Exchange and OTC = Over the Counter. How can I do this?

Upvotes: 0

Views: 92

Answers (2)

StepUp
StepUp

Reputation: 38114

One of the solution is to create lookup table:

DECLARE @Table_1 TABLE 
(ID int NOT NULL, Name VARCHAR(50) NULL)

DECLARE @Table_2 TABLE 
(ID int NOT NULL, Name VARCHAR(50) NULL)

DECLARE @Table_LookUp TABLE 
(ShortName VARCHAR(50), Name VARCHAR(50) NULL)

Then insert data:

INSERT INTO @Table_1 (
    ID, Name
)
VALUES
  (1, 'NYSE')
, (2, 'OTC')
, (3, 'OTC')


INSERT INTO @Table_2 (
    ID, Name
)
VALUES
  (1, 'New York Stock Exchange')
, (2, 'NASDAQ')
, (3, 'Over the Counter')

INSERT INTO @Table_LookUp
(
    ShortName,
    Name
)
VALUES
(   'NYSE', -- ShortName - varchar(50)
    'New York Stock Exchange'  -- Name - varchar(50)
    )
, ('OTC', 'Over the Counter')

and the query should just check whether it does not exist in look up table:

SELECT * FROM @Table_2 AS t2
WHERE NOT EXISTS 
(
    SELECT * FROM @Table_LookUp AS lp
    INNER JOIN @Table_1 AS t1 ON t1.Name = lp.ShortName
        AND lp.Name IN (t2.Name)
)

Upvotes: 1

Thom A
Thom A

Reputation: 95569

If you have some kind of lookup table, you could use a NOT EXISTS to check the value isn't in the other table:

SELECT ST.[Name]
FROM dbo.SecondTable ST
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.LookupTable LT
                       JOIN dbo.FirstTable FT ON LT.FirstTableID = FT.ID
                  WHERE LT.SecondTableID = ST.ID);

Upvotes: 4

Related Questions