Get Rows in SqlServer thoes are not mapping from other table

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

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted 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
+1