Join query produce duplicates

Question

Post reply

Join query produce duplicates

SPtrt

Mr or Mrs. 500

Points: 543
More actions
August 12, 2014 at 3:09 am

#289702

Hi, I need help on join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
CREATE TABLE #tmpExam1(
Place Varchar(MAX),
Objective Varchar(Max),
Year INT,
Month INT,
Exam1 BIGINT
)
CREATE TABLE #tmpExam2(
Place Varchar(MAX),
Objective Varchar(Max),
Year INT,
Month INT,
Exam2 BIGINT
)
CREATE TABLE #tmpExam3(
Place Varchar(MAX),
Objective Varchar(Max),
Year INT,
Month INT,
Exam3 BIGINT
)
Insert into #tmpExam1 Values('Local','Section 1(a)',2010,1,55)
Insert into #tmpExam1 Values('Local','Section 1(b)',2010,1,68)
Insert into #tmpExam1 Values('Local','section 2(a)',2010,1,91)
Insert into #tmpExam1 Values('Local','Section 2(b)',2010,1,120)
Insert into #tmpExam2 Values('Local','Section 1(a)',2010,1,'')
Insert into #tmpExam2 Values('Local','Section 1(b)',2010,1,'')
Insert into #tmpExam2 Values('Local','section 2(a)',2010,1,'')
Insert into #tmpExam2 Values('Local','Section 2(b)',2010,1,'')
Insert into #tmpExam3 Values('Local','Section 1(a)',2010,1,55)
Insert into #tmpExam3 Values('Local','Section 1(b)',2010,1,68)
Insert into #tmpExam3 Values('Local','section 2(a)',2010,1,91)
Insert into #tmpExam3 Values('Local','Section 2(b)',2010,1,120)
SELECT E1.Place, E1.Objective,E1.Year,E1.Month,E1.Exam1, E2.Exam2,E3.Exam3 FROM #tmpExam1 E1
LEFT JOIN #tmpExam2 E2 ON E2.Year = E1.Year and E2.Month = E2.Month
INNER JOIN #tmpExam3 E3 ON E3.Year = E1.Year and E3.Month = E2.Month
Group by E1.Place, E1.Objective,E1.Year,E1.Month,E1.Exam1, E2.Exam2,E3.Exam3
Above Query Results are
============ =====
Place Objective Year Month Exam1 Exam2 Exam3
Local Section 1(a) 2010 1 55 0 55
Local Section 1(a) 2010 1 55 0 68
Local Section 1(a) 2010 1 55 0 91
Local Section 1(a) 2010 1 55 0 120
Local Section 1(b) 2010 1 68 0 55
Local Section 1(b) 2010 1 68 0 68
Local Section 1(b) 2010 1 68 0 91
Local Section 1(b) 2010 1 68 0 120
Local section 2(a) 2010 1 91 0 55
Local section 2(a) 2010 1 91 0 68
Local section 2(a) 2010 1 91 0 91
Local section 2(a) 2010 1 91 0 120
Local Section 2(b) 2010 1 120 0 55
Local Section 2(b) 2010 1 120 0 68
Local Section 2(b) 2010 1 120 0 91
Local Section 2(b) 2010 1 120 0 120
Expecting Results :
==============
Place Objective Year Month Exam1 Exam2 Exam3
Local Section 1(a) 2010 1 55 0 55
Local Section 1(b) 2010 1 68 0 68
Local section 2(a) 2010 1 91 0 91
Local Section 2(b) 2010 1 120 0 120
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 1

This?

SELECT e1.Place

, e1.Objective

, e1.[Year]

, e1.[Month]

, e1.Exam1

, e2.Exam2

, e3.Exam3

FROM #tmpExam1 e1

JOIN #tmpExam2 e2

ON e1.Objective = e2.Objective

JOIN #tmpExam3 e3

ON e1.Objective = e3.Objective;

SPtrt Mr or Mrs. 500 Points: 543 More actions · Answer 2

Hi Chat,

Thanks a lot...

you simply solved. my brain not worked at that moment 🙁

Thanks & Cheers.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

Hi Chat,

Correction its Cath 🙂

you simply solved. my brain not worked at that moment 🙁

Happens!!! 😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Or you could see the other post: http://www.sqlservercentral.com/Forums/Topic1602188-391-1.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions