Get Rows in SqlServer thoes are not mapping from other table

Question

One is Demand table and another table is VarientCountry i want only thoes varient from demand table which has no country mapping in varientcountry table

Demand Table

Id Varient Country
1     v1       India
2     v2       NULL
3     v3       Nepal
4     v4        Japan

VarientCountry Table

Id Varient Country
1    v1       India
2    v1       Uk
3    v2     China
4    v1       Indonisia
5    v3       Nepal
6    v4       Egland
7    v4       Null

I want Excepted Result like as

Id Varient Country
1     v1       UK
3     v2       China
6     v4       England

Nitin Sontakke · Answer

Somehow it is not clear to me why Indonesia is excluded from your expected result. I have put together the tables and a script. Please be very specific about what should be included / excluded and why. declare @Demand Table ( [Id] integer primary key clustered ,[Varient] char (2) not null ,[Country] varchar (20) ) insert into @Demand ([Id], [Varient], [Country]) values (1, 'v1' , 'India' ) ,(2, 'v2' , NULL ) ,(3, 'v3' , 'Nepal' ) ,(4, 'v4' , 'Japan' ) declare @VarientCountry Table ( [Id] integer primary key clustered ,[Varient] char (2) not null ,[Country] varchar (20) ) insert into @VarientCountry ([Id], [Varient], [Country]) values (1, 'v1' , 'India' ) ,(2, 'v1' , 'Uk' ) ,(3, 'v2' , 'China' ) ,(4, 'v1' , 'Indonisia' ) ,(5, 'v3' , 'Nepal' ) ,(6, 'v4' , 'Egland' ) ,(7, 'v4' , NULL ) select * from @VarientCountry vc left outer join @Demand d on vc.[Varient] = d.[Varient] and vc.[Country] = d.[Country] where 1 = 1 and d.[Id] is null and vc.[Country] is not null

Get Rows in SqlServer thoes are not mapping from other table

Answers (1)