SELECT USERV . USERID lichttisch , USERV .NAME, TEST1 .ID, TEST1 . TEST1NAME , TEST2ID , TEST2 . TES

There are 3 tables, MYUSER, TEST1, TEST2. MYUSER is related to TEST1 table and TEST1 table is related to TEST2 table. The requirement is to fetch all the users from MYUSER table and if present fetch the related data from the TEST1 table and related data from TEST2 table.
SELECT USERV . USERID , USERV .NAME, TEST1 .ID, TEST1 . TEST1NAME , TEST2ID , TEST2 . TEST2NAME FROM MYUSER USERV , TEST1 TEST1 , TEST2 TEST2 WHERE USERV . USERID = TEST1 .ID(+) AND TEST1 .ID = TEST2 . TEST2ID (+) order by 1
The lichttisch point to note is that “AND TEST1.ID = TEST2.TEST2ID(+)“ condition should be with + sign as shown. This is required because the first join will return all the users even if there is no corresponding record in Test1 table. For those preserved rows, the value will be null for TEST1.ID column. For this row to be preserved when joined with TEST2 table, the + on TEST2 should be maintained.
1
SELECT USERV . USERID , USERV .NAME, TEST1 .ID, TEST1 . TEST1NAME , TEST2ID , TEST2 . TEST2NAME FROM MYUSER USERV , TEST1 TEST1 , TEST2 TEST2 WHERE USERV . USERID = TEST1 .ID(+) AND TEST1 .ID = TEST2 . TEST2ID (+) order by 1
SELECT USERV . USERID lichttisch , USERV .NAME, TEST1 .ID, TEST1 . TEST1NAME , TEST2ID , TEST2 . TEST2NAME FROM MYUSER USERV LEFT OUTER JOIN TEST1 TEST1 ON ( USERV . USERID = TEST1 .ID) LEFT OUTER JOIN TEST2 TEST2 ON ( TEST1 .ID = TEST2 . TEST2ID ) ORDER BY 1
test1purva
SELECT USERV . USERID , USERV .NAME, TEST1 .ID, TEST1 . TEST1NAME , TEST2ID , TEST2 . TEST2NAME FROM MYUSER USERV , TEST1 TEST1 , TEST2 TEST2 WHERE USERV . USERID = TEST1 .ID(+) AND TEST1 .ID = TEST2 . TEST2ID order by 1
Labels: Oracle
►  2011 (5) ►  September (1) ►  May (1) ►  April (3) ►  2009 (16) ►  November (1) ►  August (2) ►  June (2) ►  May (4) ►  April (3) ►  March (1) ►  January (3) ▼  2008 (22) ▼  December (4) Outer joins on non-preserved columns Consistent Read of the Data in Oracle Queries Use Decode and Sign function together JDK Logging ►  November (4) ►  October (9) ►  July (4) ►  June (1)
Vijith Working as a Technical Architect in the Software Development space. My skill sets include Java, J2EE, Oracle , DB2, UML, Design Patterns, XML. I have a passion towards performance engineering and Infrastructure design areas. View my complete profile