IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Join of more than one table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dibyendumandal11111

New User


Joined: 13 Feb 2017
Posts: 7
Location: india

PostPosted: Mon Feb 13, 2017 11:36 pm
Reply with quote

Hi, Need a help-
I have 4 tables tableA, TableB, TableC and TableD

Requirement is like-
Query1:
Select TableA.Col1,Sum(TableC.col2) As Sum1
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
group by TableA.Col1

Query2:
Select TableA.Col1,Sum(TableC.col2) Sum2
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
Inner join TableD
on TableC.Colx = TableD.ColY
group by TableA.Col1

Now with this 2 query I want a result set having columns TableA.Col1,Sum1,Sum2. How can I combine them in a single query.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Feb 14, 2017 11:11 am
Reply with quote

It certainly makes no sense to have two queries at first place. The sum will remain same as you are getting it from tableb, the only thing that will change is the total population. Query 2 may have less population because you added tabled to filter.
Instead of stating the problem this way , you can tell is the requirement given to you and expect us to opinion and answer and in that way you could get multiple answers or solutions.
If you still wants to persue this then you could add a union between these teo queries and add a outer select and a case to get 3 columns.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Feb 14, 2017 7:26 pm
Reply with quote

Rohit Umarjikar wrote:
The sum will remain same as you are getting it from tableb, the only thing that will change is the total population
Can you explain how the sum from 'tableb' will remain the same. And what did you mean by 'total population'.
Back to top
View user's profile Send private message
dibyendumandal11111

New User


Joined: 13 Feb 2017
Posts: 7
Location: india

PostPosted: Tue Feb 14, 2017 10:47 pm
Reply with quote

Dear Rohit,

How can you say SUM will be same for both the queries?... Second one have an extra inner join.

Anyway with some help I am able to get the desired result.
Consolidated query is like-

Code:
Select T1.ColXX,Sum1,Sum2
from
(
(Select TableA.Col1 as ColXX,Sum(TableC.col2) As Sum1
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
group by TableA.Col1 ) as T1

inner join
(Select TableA.Col1 as ColXX,Sum(TableC.col2) Sum2
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
Inner join TableD
on TableC.Colx = TableD.ColY
group by TableA.Col1 ) as T2
on T1.ColXx= T2.ColXX)

Any better option is still welcome.
coded
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Feb 14, 2017 11:58 pm
Reply with quote

dibyendumandal11111,

Welcome to the forums.

If these tables have large amount of data, you might not want to repeat JOINing A,B, and C in your query.
How about trying to INNER JOIN A,B, and C , LEFT JOIN with D, then work with the result set on an outer query to find Sum1 and Sum2.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Feb 15, 2017 11:24 pm
Reply with quote

It looks like , I am too much loaded with the work, so please ignore my earlier comment of having "Same Results".
But yes you could have straight LEFT JOIN on tableD and get the second SUM form there and no need to have outer SELECT or anything. Compare the results with your other solution.
Code:
Select a.Col1,Sum1,b.Sum2
 From TableA a
 Inner join TableB
 on TableA.Colx = TableB.ColY
 Inner join TableC
 on TableB.Colx = TableC.ColY
 left outer join TableD b
 on TableC.Colx = TableD.ColY
group by a.Col1
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Feb 15, 2017 11:28 pm
Reply with quote

Rohit Umarjikar,

Where did 'Sum1' and 'Sum2' come from? Do you realize those are not column names and have to be calculated.
Do you think 'GROUP BY' will work without an aggregate function in the select clause in this context?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top