SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Struggling on Duplicates while Join Three Table Produce


Struggling on Duplicates while Join Three Table Produce

Author
Message
SPtrt
SPtrt
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 109
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
TEJABI
TEJABI
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 168
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
TEJABI
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 168
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
TEJABI
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 168
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][left][/left]
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17232 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
kbhanu15
kbhanu15
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1473 Visits: 336
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search