By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
)
INSERT INTO FirstTable VALUES(1,'Bam','1234'),(2,'Prish','3433'),(3,'Tomato','4545')
INSERT INTO SecondTable VALUES(1,'Sister',NULL),(2,'Mother','PS3433'),(3,'Nomad',NULL),(3,'Nothing','PS4322')
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
| Id | Name | Type |
|---|---|---|
| 1 | Bam | 1234 |
| 3 | Tomato | 4545 |