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
 

 

Need a query where I can join all the three tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need a query where I can join all the three tables
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    Post subject:
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    Post subject: join
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    Post subject: Another Query
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: 1452
Location: Chicago, IL

PostPosted: Sun Sep 16, 2007 12:01 pm    Post subject: Reply to: Need a query where I can join all the three tables
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    Post subject:
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    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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


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