Not matched records in sql using joins

venkat

8y889

Hi all ,

Below i have two tables , 1st table contain 12 records and second one contain 8 records. now i need to get remain 4 records only not with 8 records using joins in sql. i have tried below query but still am not getting .please help on this

Select DISTINCT it.ShapeName,it.Intervalyear,it.IntervalMonth from @yearTable yt

left JOIN @IntervalTable it on yt.NumDays=it.IntervalNumDays

where it.Intervalyear is null and it.IntervalNumDays='2018'

6 Replies

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

Jayesh Vaghasiya8y ago
Select DISTINCT it.ShapeName,it.Intervalyear,yt.year,yt.Month
from @yearTable yt
left JOIN @IntervalTable it on yt.NumDays=it.IntervalNumDays and yt.month=it.IntervalMonth and yt.year=it.Intervalyear
where it.Intervalyear is null and yt.year='2018'
0
Gaurav Raut8y ago
Hello Venkat,
please try below mentioned query
1]
Select * from [yearTable] yt

left JOIN [IntervalTable] it on yt.year=it.Intervalyear and it.Intervalmonth = month

2]

select * from yearTable where month not in (select intervalmonth from IntervalTable where [intervalyear] = 2018)

if you make the jion on numdays you will get multiple rows and you are filtering those rows by using distinct keyword.
0
Packiaraj Santhiyagu8y ago
Hi Venkat,

Please try this query.
select * from IntervalMonth where [year] = 2018 and [month] not in (select [IntervalMonth] from IntervalYear where [IntervalYear] = 2018)
0
rajendra singh8y ago
Please try below,

Let me know if you know more help.
select * from IntervalMonth where year = 2018 and month not in (select IntervalMonth from IntervalYear where IntervalYear = 2018)
Please accept answer if you found this useful.
0
venkat8y ago
Hi Rajendra,

Based on above screen shot i need to display below out put

0
rajendra singh8y ago
Hi Venkat,

Could you please tell what data you need to show.

I am confused littile bit if you need only 8 records than why you apply to join, so please write what data you want to retrive. and why you were not getting data just remove where condition you will able to see data.
0