| Author |
Message |
arps4u
New User
Joined: 03 Jun 2006
Posts: 3
|
|
|
|
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 |
|
 |
References
|
 Posted: Fri Apr 25, 2008 8:54 pm Post subject: Re: Left Join in DB2 consisting 3 tables |
|
|
|
|
Bharath Bhat
New User
Joined: 20 Mar 2008
Posts: 29
Location: chennai
|
|
|
|
| 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 |
|
|
ashimer
Warnings : 1
Active User
Joined: 13 Feb 2004
Posts: 113
|
|
|
|
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 |
|
|
ashimer
Warnings : 1
Active User
Joined: 13 Feb 2004
Posts: 113
|
|
|
|
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 |
|
|
arps4u
New User
Joined: 03 Jun 2006
Posts: 3
|
|
|
|
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 |
|
|
ashimer
Warnings : 1
Active User
Joined: 13 Feb 2004
Posts: 113
|
|
|
|
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 |
|
|
|
|
