View previous topic :: View next topic
|
Author |
Message |
venosol Warnings : 1 New User
Joined: 16 Nov 2006 Posts: 43 Location: Bangalore
|
|
|
|
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 |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
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 |
|
|
logeswarank Warnings : 1 New User
Joined: 15 Oct 2006 Posts: 22 Location: Chennai
|
|
|
|
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 |
|
|
logeswarank Warnings : 1 New User
Joined: 15 Oct 2006 Posts: 22 Location: Chennai
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
|