Struggling on Duplicates while Join Three Table Produce

Question

Post reply

Struggling on Duplicates while Join Three Table Produce

SPtrt

Mr or Mrs. 500

Points: 543
More actions
August 12, 2014 at 2:58 am

#297880

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
============ =====
PlaceObjectiveYearMonthExam1Exam2Exam3
LocalSection 1(a)2010155055
LocalSection 1(a)2010155068
LocalSection 1(a)2010155091
LocalSection 1(a)20101550120
LocalSection 1(b)2010168055
LocalSection 1(b)2010168068
LocalSection 1(b)2010168091
LocalSection 1(b)20101680120
Localsection 2(a)2010191055
Localsection 2(a)2010191068
Localsection 2(a)2010191091
Localsection 2(a)20101910120
LocalSection 2(b)20101120055
LocalSection 2(b)20101120068
LocalSection 2(b)20101120091
LocalSection 2(b)201011200120
Expecting Results :
==============
PlaceObjectiveYearMonthExam1Exam2Exam3
LocalSection 1(a)2010155055
LocalSection 1(b)2010168068
Localsection 2(a)2010191091
LocalSection 2(b)201011200120
Thanks in advance

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

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

TEJABI Old Hand Points: 368 More actions · Answer 1

If u need that result ,u have to change the joins AS I mention in below

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 = E1.Month and E1.Objective =E2.Objective

Inner JOIN #tmpExam3 E3 ON E3.Year = E1.Year and E3.Month = E2.Month and E1.Objective =E3.Objective

Group by E1.Place, E1.Objective,E1.Year,E1.Month,E1.Exam1, E2.Exam2,E3.Exam3

TEJABI Old Hand Points: 368 More actions · Answer 2

If u need that result ,u have to change the joins AS I mention in below

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 = E1.Month and E1.Objective =E2.Objective

Inner JOIN #tmpExam3 E3 ON E3.Year = E1.Year and E3.Month = E2.Month and E1.Objective =E3.Objective

Group by E1.Place, E1.Objective,E1.Year,E1.Month,E1.Exam1, E2.Exam2,E3.Exam3

TEJABI Old Hand Points: 368 More actions · Answer 3

If u need that result ,u have to change the joins AS I mention in below

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 = E1.Month and E1.Objective =E2.Objective

Inner JOIN #tmpExam3 E3 ON E3.Year = E1.Year and E3.Month = E2.Month and E1.Objective =E3.Objective

Group by E1.Place, E1.Objective,E1.Year,E1.Month,E1.Exam1, E2.Exam2,E3.Exam3[/quote]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT

e1.Place,

e1.Objective,

e1.[Year],

e1.[Month],

e1.Exam1,

e2.Exam2,

e3.Exam3

FROM #tmpExam1 e1

LEFT JOIN #tmpExam2 e2

ON e2.Place = e1.Place

AND e2.[Year] = e1.[Year]

AND e2.Objective = e1.Objective

AND e2.[Month] = e2.[Month]

INNER JOIN #tmpExam3 e3

ON e3.Place = e1.Place

AND e3.Objective = e1.Objective

AND 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

ORDER BY

e1.Place,

e1.Objective,

e1.[Year],

e1.[Month],

e1.Exam1,

e2.Exam2,

e3.Exam3

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 5

HI

Try this method:

SELECT E1.Place, E1.Objective,E1.Year,E1.Month,E1.Exam1, E2.Exam2,E3.Exam3

FROM #tmpExam1 E1

inner join #tmpExam2 E2 on E1.objective = E2.objective

inner join #tmpExam3 E3 on E1.objective = E3.objective