CONVERT WHERE CLAUSE TO OUTER JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CONVERT WHERE CLAUSE TO OUTER JOIN

Author

Topic

plpl2004
Starting Member

4 Posts

Posted - 2008-05-28 : 00:28:40

I have a SQL query where am using WHERE clause as a result of which the NULL values are getting filtered...can u please help me to tranform this query into LEFT OUTER JOINS so as to avoid this filtration....my query is

SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE FROM EALPS_DRWREVSPLJTS

A,EALPS_DRW_REV_SPL B,EALPS_DRW_REV_LINE C,EALPS_WELDTESTCRIT D,EALPS_ACTV_SEQ E WHERE B.SPOOL_ID=A.SPOOL_ID AND

B.LINE_ID=C.LINE_ID AND D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID AND E.ACTIVITY_CODE='VT' AND E.JOINT_ID=A.JOINT_ID

Thanks and Regards,
parama.

parama laha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 00:47:18

[code]SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE
FROM EALPS_DRWREVSPLJTS A
LEFT JOIN EALPS_DRW_REV_SPL B
ON B.SPOOL_ID=A.SPOOL_ID
LEFT JOIN EALPS_DRW_REV_LINE C
ON B.LINE_ID=C.LINE_ID
LEFT JOIN EALPS_WELDTESTCRIT D
ON D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID
LEFT JOIN EALPS_ACTV_SEQ E
ON E.JOINT_ID=A.JOINT_ID
WHERE (E.ACTIVITY_CODE='VT' OR E.ACTIVITY_CODE IS NULL)[/code]

Also have a look at LEFT JOINs in Books online to understand how they work

plpl2004
Starting Member

4 Posts

Posted - 2008-05-28 : 01:08:44

quote:

Originally posted by visakh16

SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE 
FROM EALPS_DRWREVSPLJTS A
LEFT JOIN EALPS_DRW_REV_SPL B
ON B.SPOOL_ID=A.SPOOL_ID 
LEFT JOIN EALPS_DRW_REV_LINE C
ON B.LINE_ID=C.LINE_ID
LEFT JOIN EALPS_WELDTESTCRIT D
ON D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID 
LEFT JOIN EALPS_ACTV_SEQ E 
ON E.JOINT_ID=A.JOINT_ID
WHERE (E.ACTIVITY_CODE='VT' OR E.ACTIVITY_CODE IS NULL)

Also have a look at LEFT JOINs in Books online to understand how they work

parama laha

plpl2004
Starting Member

4 Posts

Posted - 2008-05-28 : 01:09:49

hey thanks a lot for ur query but it is giving me an error as:

columns of one table that is referenced in one outer join may not be referenced in an ON clause of another outer join

parama laha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 01:33:01

quote:
Originally posted by plpl2004

hey thanks a lot for ur query but it is giving me an error as:

columns of one table that is referenced in one outer join may not be referenced in an ON clause of another outer join

parama laha

Are you using MS SQL Server?

plpl2004
Starting Member

4 Posts

Posted - 2008-05-28 : 01:48:15

I am using oracle.

parama laha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 02:05:25

quote:
Originally posted by plpl2004

I am using oracle.

parama laha

Ok. then you're in wrong forum. This forum is for MS SQL Server. Post in some oracle forums if want accurate soln to this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources