IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need a query where I can join all the three tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
venosol
Warnings : 1

New User


Joined: 16 Nov 2006
Posts: 43
Location: Bangalore

PostPosted: Thu Sep 13, 2007 10:17 am
Reply with quote

Hi,

I need some sample queries on this request.

I have three tables say A,B and C. Three tables contain two columns say emp1 and sal1. These two columns are primary key for all the tables. Also emp1 contains some values that start with "S" in all the tables. One table C contains a specila column say Id1. One table B conains location column.

Now I need a query where I can join all the three tables extracting rows containing "s" order by Id1 and belong to particular location say 'chennai'.

I tried joining two tables each time using outer join. but it is long process. can you tell me any simple query wher I can get exact data.

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

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Thu Sep 13, 2007 10:46 am
Reply with quote

Try out this one...

A.EMP1,C.ID1
from A
join B
ON A.emp1 = B.emp1
AND A.SAL1 = B.SAL1

join C
ON A.emp1 = C.emp1
AND A.SAL1 = C.SAL1
WHERE A.EMP1 like 'S%'
and B.LOCATION = 'Chennai'

order by id1
Back to top
View user's profile Send private message
logeswarank
Warnings : 1

New User


Joined: 15 Oct 2006
Posts: 22
Location: Chennai

PostPosted: Thu Sep 13, 2007 11:24 am
Reply with quote

Hi VENUGOPAL,

Code:
SELECT EMP1,SAL1 FROM A WHERE SUBSTR(EMP1,1,1)='S'
INNER JOIN (SELECT EMP1,ID1 FROM B INNER JOIN SELECT LOCA FROM
C WHERE LOCA='CHENNAI')

If ITS wrong correct me.
Back to top
View user's profile Send private message
logeswarank
Warnings : 1

New User


Joined: 15 Oct 2006
Posts: 22
Location: Chennai

PostPosted: Thu Sep 13, 2007 3:26 pm
Reply with quote

Code:
SELECT A.emp1,B.ID1,C.LOCA FROM A,B,C WHERE  C.LOCA='CHENNAI' and SUBSTR(B.ID1,1,1)='z'
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Sun Sep 16, 2007 12:01 pm
Reply with quote

Quote:
Now I need a query where I can join all the three tables extracting rows containing "s" order by Id1 and belong to particular location say 'chennai'.

Venu,

Now why you want to join three tables when you want to pick up data from only two tables. I would write my query as below for your requirement.

Let's say in your example table A's name is Emp, table B's name is Loc, table C's name is ID.

Code:
SELECT    Y.EMP1, Y.SAL1, Y.LOCATION, Z.ID1
  FROM   ID Y,
       LOC Z
WHERE   Y.EMP1    = Z.EMP1
     AND   Y.SAL1    = Z.SAL1
     AND   Y.EMP1   LIKE 'S%'
     AND   Y.LOCATION   = 'CHENNAI';


The better you put your requirement, better you get answer on... HTH.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Sep 16, 2007 7:14 pm
Reply with quote

I write my joins like the last 2 unless I really can't get the data without specifying LEFT/RIGHT INNER/OUTER.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top