Duplicate rows occuring when use left join

Question

1.00/5 (1 vote)

See more:

my stored procedure is this

SQL

create procedure EnquiryAdmission as
create table #Calendar
(Months date)
insert INTO #Calendar VALUES('1/1/2014'),('2/1/2014'),('3/1/2014'),('4/1/2014'),('5/1/2014'),('6/1/2014'),('7/1/2014'),('8/1/2014'),('9/1/2014'),('10/1/2014'),('11/1/2014'),('12/1/2014')

BEGIN 
	SELECT cal.Months,DISTINCT COUNT(enqno) AS Enquiries,DISTINCT count(learnerid)AS Admissions
FROM #calendar cal
LEFT JOIN enquiry e 
ON Month(cal.Months) = Month([enquiry date])
LEFT JOIN  admission
ON  Month(cal.Months)=Month(Date)
WHERE (YEAR([enquiry date])=YEAR(GetDate())) OR [enquiry date] IS NULL
AND (YEAR(date)=YEAR(GetDate())) OR date IS NULL
GROUP BY cal.Months
END

but it gives duplicate records. It should give exact count. What can I do to solve this problem

Posted 9-Oct-14 19:49pm

Member 11026061

Updated 9-Oct-14 20:02pm

King Fisher

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11026061 · Accepted Answer · 2014-10-09T20:55:00

SQL

CREATE procedure EnquiryAdmission as
create table #Calendar
(Months date)
insert INTO #Calendar VALUES('1/1/2014'),('2/1/2014'),('3/1/2014'),('4/1/2014'),('5/1/2014'),('6/1/2014'),('7/1/2014'),('8/1/2014'),('9/1/2014'),('10/1/2014'),('11/1/2014'),('12/1/2014')

BEGIN
 SELECT datename(mm,cal.months), COUNT(DISTINCT enqno) AS Enquiries,count(DISTINCT learnerid)AS Admissions
FROM #calendar cal
LEFT JOIN enquiry e
ON Month(cal.Months) = Month([enquiry date]) AND ((YEAR([enquiry date])=YEAR(GetDate())) OR [enquiry date] IS NULL)
LEFT JOIN  admission
ON  Month(cal.Months)=Month(Date) AND ((YEAR(date)=YEAR(GetDate())) OR date IS NULL)

GROUP BY cal.Months
END

King Fisher · Accepted Answer · 2014-10-09T20:07:00

Solution 1

better you can use INNER JOIN.
before your Write query know about What is LEFT,RIGHT,INNER JOINS

Read this Article

http://www.dotnet-tricks.com/Tutorial/sqlserver/W1aI140312-Different-Types-of-SQL-Joins.html[^]

Posted 9-Oct-14 20:07pm

King Fisher

Duplicate rows occuring when use left join

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Duplicate rows occuring when use left join

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us