Not matched records in sql using joins

Question

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'

Jayesh Vaghasiya · Answer

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'

Gaurav Raut · Answer

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.

Packiaraj Santhiyagu · Answer

Hi Venkat, Please try this query. select * from IntervalMonth where [year] = 2018 and [month] not in (select [IntervalMonth] from IntervalYear where [IntervalYear] = 2018)

rajendra singh · Answer

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.

venkat · Answer

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

rajendra singh · Answer

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.

Not matched records in sql using joins

Answers (6)