Compare two tables for a matching value from the respective columns and identify records missing

I have two tables and data like below:

I want to compare two table’s columns “Type and MatchType” and identify the Ids of first table where Type is missing in MatchType. “Type” and “MatchType” are the only common column in both tables in case if we can use join. For example, FirstTable column value in second row matches second row of SecondTable, the comparison should be like FirstTable always have numbers and second table have 2 characters before the same numbers, so we should ignore the characters and check ONLY for numbers, and if both match, then ignore that ID from first table. In this case example data below, my output should have Ids 1 and 3 from FirstTable as both of those record’s “Type” are NULL or not present in the SecondTable.

CREATE TABLE [dbo].[FirstTable](
    [Id] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [Type] [varchar](50) NULL
)

CREATE TABLE [dbo].[SecondTable](
    [ID] [int] NULL,
    [Relation] [varchar](50) NULL,
    [MatchType] [nvarchar](50) NULL
)

Id  Name    Type
1   Bam     1234
2   Prish   3433
3   Tomato  4545


ID  Relation    MatchType
1   Sister     NULL
2   Mother     PS3433
3   Nomad      NULL
4   Nothing    PS4322

Expected Result

Ids (From first table as the respective ID’s Types are not present in the SecondTable’s MatchType – Remember to remove the 2 characters in front in the second table’s MatchType values and just compare the number)

1
3

I tried using PATINDEX but it doesn’t seem to work or I am doing something wrong.

Answer

Try This

SELECT F.* 
FROM FirstTable F LEFT JOIN (SELECT RIGHT(MatchType,LEN(MatchType)-2) MT FROM SecondTable) S ON F.Type = S.MT  
WHERE S.MT IS NULL

FIDDLE DEMO

Advertisement

Answer