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
 

 

Left Join in DB2 consisting 3 tables

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

New User


Joined: 03 Jun 2006
Posts: 4

PostPosted: Fri Apr 25, 2008 8:54 pm    Post subject: Left Join in DB2 consisting 3 tables
Reply with quote

Hi,

I need to query 3 tables to look for the existence of a record in all the 3 tables. I know we can use left join for the 2 tables, but can someone suggest how to use it when 3 tables are present. Also, if some other way is available, kindly let me know.

Thanks,
Back to top
View user's profile Send private message

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Apr 25, 2008 9:28 pm    Post subject:
Reply with quote

Code:

SELECT A.TAB1.ATTRA
           ,B.TAB2.ATTRB
           ,C.TAB3.ATTRC
  FROM  TAB1 A
           ,TAB2 B
           ,TAB3 C
WHERE ATTRB1=ATTRA1 AND
           ATTRC1=ATTRA1 AND
           ATTRA1= VARIABLE

 

TAB1,TAB2 and TAB3 are the 3 tables.
ATTRA*-->attributes of TAB1
ATTRB*-->attributes of TAB2
ATTRc*-->attributes of TAB3
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 25, 2008 9:45 pm    Post subject:
Reply with quote

Hi Arps4u ,

try this ..

Code:


SELECT A.ID ,
B.JOB ,
C.NAME

FROM STAFF_A A
JOIN
STAFF_B B
ON A.ID = B.ID
JOIN
STAFF_C C
ON B.ID = C.ID
WHERE C.NAME =  'ASHIMER'
ORDER BY A.ID
,B.JOB;

Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 25, 2008 9:48 pm    Post subject:
Reply with quote

Oops sorry i gave u inner join example .. change the join clause to left outer join

Code:


SELECT A.ID ,
B.JOB ,
C.NAME

FROM STAFF_A A
LEFT OUTER JOIN
STAFF_B B
ON A.ID = B.ID
LEFT OUTER JOIN
STAFF_C C
ON B.ID = C.ID
WHERE C.NAME =  'ASHIMER'
ORDER BY A.ID
,B.JOB;
Back to top
View user's profile Send private message
arps4u

New User


Joined: 03 Jun 2006
Posts: 4

PostPosted: Tue Apr 29, 2008 12:19 am    Post subject: Reply to: Left Join in DB2 consisting 3 tables
Reply with quote

Thanks for all the replies it worked...I have one more query though, suppose I want a count of all the records for one particular field which is present in table A and not in table B & table C, how can I manipulate this Left Join query to work ?

e.g -- I have 10 records under the column PLAYER in Table A. Now I want the count of records that are not present in Table B and Table C i.e it's only present in Table A.

Kindly let me know if I need to be more specific.

Thanks,
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 2:25 pm    Post subject:
Reply with quote

Hi,
Try this ..

Code:


SELECT COUNT(PLAYER) FROM TABLEA WHERE PLAYER NOT IN
(SELECT PLAYER FROM TABLEB UNION SELECT PLAYER FROM TABLEC);

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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 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 SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am


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