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
 

 

3 table left outer join query

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

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Thu Jan 10, 2013 5:04 pm    Post subject: 3 table left outer join query
Reply with quote

Hi i have 3 table join question. Please advise.

Code:

There are 3 tables
Table A
--------
GR_ID      E_NM    REC_ID
123   AAA   03
123   BBB   03
124   DDD   04

Table B
--------
CL_ID    E_NM   REC_ID    DAT_C
Client1      AAA   03   TT
Client2      AAA   03   XX

Table C
--------
GR_ID   CL_ID
123   Client1
123   Client2
124   Client3


I need the E_NM associated with CL_ID = Client1 only
The result set should have the following data
Code:

CL_ID,    E_NM,    REC_ID,    DAT_C
Client1   AAA         03        TT
Client1   BBB         03         ----


the query used now.

Select  CL_ID, E_NM, REC_ID, DAT_C    from
Table A left Join
Table B on
A.E_NM = B.E_NM
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)

The result that i get is

CL_ID,    E_NM,    REC_ID,    DAT_C
Client1   AAA          03        TT
Client2   AAA          03        XX
Client1   BBB          03        ----


I dont want to get the line from Client 2. please advise how the query can be modified?

[/code]
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 5:12 pm    Post subject:
Reply with quote

You where clause is wrong

Code:
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)


Filter based on the field CL_ID

Code:
where CL_ID = 'Client1'
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Thu Jan 10, 2013 5:23 pm    Post subject:
Reply with quote

Hi Pandora, the table A can have more GRP_ID under Client 1

Code:

Table A
--------
GR_ID      E_NM    REC_ID
123            AAA    03
123            BBB    03
124            DDD   04
125            CCC   05

Table C
---------
GR_ID   CL_ID
123        Client1
125        Client1


I did not include the information before so that it wouldnt confuse. this is why i have used GR_ID in the where clause.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 6:00 pm    Post subject:
Reply with quote

Why not try this?

Code:
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)
          and   CL_ID = 'Client1'
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Thu Jan 10, 2013 6:13 pm    Post subject:
Reply with quote

when i do that i get only the line

Code:

CL_ID,    E_NM,    REC_ID,    DAT_C
Client1   AAA          03           TT


I need to get this line as well
Code:

Client1   BBB         03         ----


Thanks for your patience in this.

Demangled
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Thu Jan 10, 2013 6:33 pm    Post subject: Reply to: 3 table left outer join query
Reply with quote

Please use the Preview button so that you are happy with how you post looks before using Submit.
Back to top
View user's profile Send private message
Chandu C

New User


Joined: 22 Jun 2012
Posts: 2
Location: INDIA

PostPosted: Thu Jan 10, 2013 6:41 pm    Post subject:
Reply with quote

Hi CJ

Could you please check if any info is missing regarding the requirement.
Would you mind telling the relationship between the 3 tables.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 6:48 pm    Post subject:
Reply with quote

Shucks I screwed it up let me rethink
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 7:33 pm    Post subject:
Reply with quote

For the query you mentioned I got the output

I used the same set of data in the table

Code:
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT  B.CL_ID, B.E_NM, B.REC_ID, B.DAT_C    FROM                      00000199
TABA A LEFT JOIN                                                        00000299
TABB B ON                                                               00000399
A.E_NM = B.E_NM                                                         00000499
WHERE GR_ID = (SELECT GR_ID FROM TABC C WHERE CL_ID = 'CLIENT1');       00000599
---------+---------+---------+---------+---------+---------+---------+---------+
CL_ID    E_NM  REC_ID  DAT_C
---------+---------+---------+---------+---------+---------+---------+---------+
CLIENT2  AAA   03      XX
CLIENT1  AAA   03      TT
-------  ----  ------  -----
-------  ----  ------  -----
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Fri Jan 11, 2013 11:26 am    Post subject:
Reply with quote

Hi Pandora,

the result set gives for both client 1 and client2. i need only for Client 1

Code:

CL_ID,    E_NM,    REC_ID,    DAT_C
Client1   AAA         03        TT
Client1   BBB         03         ----



Hi Chandu, the relationship b/w the 3 tables is as follows.
The first table stores group ID with various e_nm under each group ID. the 2nd table contains more information pertaining to each E_NMs which were present in the first table.
Now 3rd table stores Group ID and CLient id. 1 groupd id can belong to more than 1 client.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Jan 11, 2013 2:10 pm    Post subject:
Reply with quote

Hi,

Try this if it works as expected. This is untested

Code:
Select  C.CL_ID, A.E_NM, A.REC_ID, B.DAT_C   
from Table C
,Table A left Join
Table B on
A.E_NM = B.E_NM
AND B.CL_ID = Client1
WHERE C.GR_ID = A.GR_ID
 AND  C.CL_ID = Client1


Regards,
Chandan
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Fri Jan 11, 2013 4:56 pm    Post subject:
Reply with quote

HI Chandan,

thanks for helping. However, the query did not give the desired result.

I tried this query out and it worked.

Code:


Select  CL_ID, E_NM, REC_ID, DAT_C    from
Table A left Join
Table B on
A.E_NM = B.E_NM and B.CL_ID = Client1
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)




Thank you Chandan, pandora for your help on this. [/code]
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Jan 11, 2013 5:19 pm    Post subject:
Reply with quote

Hi,

Can you share the output of query posted by me?
I am just curious what are the wrong results

and for your query just change like below

Code:
Select  CL_ID, E_NM, REC_ID, DAT_C    from
Table A left Join
Table B on
A.E_NM = B.E_NM and B.CL_ID = Client1
where GR_ID IN (select GR_ID from table C where CL_ID = CLient1)


So that with IN clause it will work even if Subquery gives mutiple rows

Regards,
Chandan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jan 14, 2013 3:24 pm    Post subject:
Reply with quote

Code:
select *
from       Table_C C
INNER JOIN TABLE_A A on A.GR_ID = C.GR_ID
 LEFT JOIN TABLE_B B on B.CL_ID = C.CL_ID and B.E_NM = A.E_NM
where C.CL_ID = 'Client1'
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


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