-2

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jasmine
  • 5,186
  • 16
  • 62
  • 114

1 Answers1

1

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

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • Sorry, one more silly question if you get time any later. How to make the number 2 that you are subtracting as a generic. Lets say, what if by bad chance I had 3 or 4 charactes etc etc, just pick the number alone. I think we can use patindex[0-9] but I was unable to frame that correctly. – Jasmine Nov 06 '19 at 11:11
  • 1
    @Learner When you are not sure about the string length then go for `PATINDEX` as mentioned here `SELECT MatchType,SUBSTRING(MatchType, PATINDEX('%[0-9]%', MatchType), PATINDEX('%[0-9][^0-9]%', MatchType + 't') - PATINDEX('%[0-9]%', MatchType) + 1) AS Number FROM SecondTable`. Also you can see the [**DEMO**](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=651d7e6d03bab159135a93bea15ecd0c) here. – Vignesh Kumar A Nov 06 '19 at 11:18