| View previous topic :: View next topic |
| Author |
Message |
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
 Posted: Thu Jul 30, 2009 10:30 am Post subject: Question on Query |
 |
|
i have 4 tables. 3 of them have to be inner joined to obtain the list of accounts and some account details after applying a lot of filter conditions on the 3 tables. Then i need to do left outer join with the 4th table on the account number.
The 3 tables for inner join have 100000 rows each and the fourth table is a Child table which contains 85000 rows. In all the tables ACCT_No is the common reference.
When i apply the filter conditions on the 3 tables only 5 out of 100000 records are qualified and i would like to left outer join only these 5 or less rows with the 4th table.
My query is, is it possible to filter out the 5 rows alone in table 4 before i do left outer join?
A Example
| Code: | SELECT RESULT1.COLX
,RESULT1.COL1
,RESULT1.COL2
,RESULT1.COL3
,RESULT2.COL4
FROM
( ( SELECT A.COLX
,A.COL1
,B.COL2
,C.COL3
FROM TABLE_A A
,TABLE_B B
,TABLE_C C
WHERE (There are a number of filter conditions on the 3 tables)
AND A.COLX = B.COLY
AND B.COLY = C.COLZ
) AS RESULT1
LEFT OUTER JOIN
(
SELECT D.COLX
,D.COL4
FROM TABLE_D D
WHERE (some filter conditions on Table_D)
--What i wanted to do here is instead of having the whole table to participate in the LEFT OUTER JOIN
--i would want only the 5 rows to participate.
) AS RESULT2
ON RESULT1.COLX = RESULT2.COLX
) |
Can someone please help.
Did i make my question Clear..
_________________
Thanks |
|
| Back to top |
|
 |
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Tue Aug 04, 2009 8:26 am Post subject: |
|
|
Its not possible to do this with SQL or Dint i make my question clear..
_________________
Thanks |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Wed Aug 05, 2009 3:36 am Post subject: |
|
|
To my eyes your query looks fine and should do what you want. Is EXPLAIN showing, that your first subselect is executed as the first query block?
regards
Christian |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
