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
 

 

Connecting 3 tables, and getting the output

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

New User


Joined: 04 May 2007
Posts: 20
Location: INDIA

PostPosted: Thu Nov 11, 2010 12:08 am    Post subject: Connecting 3 tables, and getting the output
Reply with quote

I have three tables A, B, C. All these 3 tables have a common key column, say KEY.

Table - A, Columns - KEY, COL1, COL2
Table - B, Columns - KEY, COL1, COL2
Table - C, Columns - KEY, COL1, COL2

In the output result set, I wish to have few columns from A and few columns from C which has A.KEY = C.KEY AND a satisfying condition in table B.

I tried the below query, and it seems it is going to take days. Can someone help me fine-tune/re-write this query?

SELECT A.COL1, A.COL2, C.COL1
FROM A INNER JOIN C
ON
A.KEY = C.KEY
WHERE
A.KEY IN (SELECT KEY FROM B WHERE COL1 IN ('VAL1','VAL2')
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 11, 2010 1:22 am    Post subject:
Reply with quote

Hello,

Suggest you consider eliminating unwanted rows from table B and then join only the rows you want with tables A/C. . .
Back to top
View user's profile Send private message
Mahendran3784

New User


Joined: 04 May 2007
Posts: 20
Location: INDIA

PostPosted: Thu Nov 11, 2010 1:32 am    Post subject:
Reply with quote

d.sch.,
Yeah, we dont want any rows from table B in the results set, just that table will be used for an extract criteria. I am trying the below query now. Will update you if I am successful?
SELECT A.COL1, A.COL2, C.COL1 FROM A INNER JOIN B ON A.KEY=B.KEY INNER JOIN C ON A.KEY=C.KEY WHERE B.COL1 IN ("VAL1", "VAL2")
I see this is also taking more time.
Thanks!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 11, 2010 2:19 am    Post subject:
Reply with quote

Hello,

Just because you use table criteria to eliminate most of the rows does not imply that any of the results have to be from table B.

If you don't want to use this kind of sql, you can always extract the useful rows from table to a sequential file and use this file to drive the sql that actually selects the rows you do want.
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 find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm


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