Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Join of more than one table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dibyendumandal11111

New User


Joined: 13 Feb 2017
Posts: 2
Location: india

PostPosted: Mon Feb 13, 2017 11:36 pm    Post subject: Join of more than one table
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

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Tue Feb 14, 2017 11:11 am    Post subject:
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: 2276
Location: @my desk

PostPosted: Tue Feb 14, 2017 7:26 pm    Post subject:
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: 2
Location: india

PostPosted: Tue Feb 14, 2017 10:47 pm    Post subject: Reply to: Join of more than one table
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: 2276
Location: @my desk

PostPosted: Tue Feb 14, 2017 11:58 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Wed Feb 15, 2017 11:24 pm    Post subject:
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: 2276
Location: @my desk

PostPosted: Wed Feb 15, 2017 11:28 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us