Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 VBS and VB, COBOL syntax is the same ... natt.sut COBOL Programming 3 Sun Nov 12, 2017 6:36 am
No new posts Copy selected columns from 2 input fi... Poha Eater DFSORT/ICETOOL 3 Thu Nov 02, 2017 3:43 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts Where is output from PARM ABEXIT (SNA... packerm CA Products 3 Thu Sep 28, 2017 4:06 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us