EXISTS - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

EXISTS

Author

Topic

kprasadreddy
Starting Member

41 Posts

Posted - 2005-04-04 : 16:09:18

Can I use where Crs_NB and CRS_VRSN_NB exists in (select crs_nb,crs_vrsn_nb as below....

SELECT DISTINCT LD180.STDNT_NB,
ld010.USER_ID_CD,
LD010.FIRST_NM,
LD010.LAST_NM,
LD010.HIRE_DT,
LD180.CRS_NB,
LD180.CRS_VRSN_NB,
LD030.CRS_NM,
LD180.ENTYCHLNGSCRVAL_PC,
LD180.MSTRCHLNGSCRVAL_PC
FROM ZLDT180_SCR LD180
INNER JOIN ZLDT010_STDNT LD010
ON LD010.STDNT_NB = LD180.STDNT_NB
INNER JOIN ZLDT030_CRS LD030
ON LD030.CRS_NB = LD180.CRS_NB
AND lD030.CRS_VRSN_NB = LD180.CRS_VRSN_NB
INNER JOIN ZLDT075_CRRCLM_CRS LD075
ON LD075.CRS_NB = LD180.CRS_NB
AND LD075.CRS_VRSN_NB = LD180.CRS_VRSN_NB
WHERE EXISTS (SELECT DISTINCT LD075.CRS_NB,
LD075.CRS_VRSN_NB
FROM ZLDT075_CRRCLM_CRS LD075
INNER JOIN ZLDT030_CRS LD030
ON LD075.CRS_NB = LD030.CRS_NB
AND LD075.CRS_VRSN_NB = LD030.CRS_VRSN_NB
WHERE LD030.CRS_STAT_CD = 1
AND LD075.CRRCLM_CD ='1'
AND LD075.CRRCLM_TRACK_CD = '23'
AND (LD075.LOC_CD = 'BA' OR LD075.LOC_CD ='ZZ'))
AND LD010.USER_ID_CD ='tandre14 '
AND LD180.QSTN_CATG_CD IN ('EC','MC')
AND LD075.CRRCLM_CD ='1'
AND LD075.CRRCLM_TRACK_CD ='23'
AND (LD075.LOC_CD = 'BA' OR LD075.LOC_CD ='ZZ')
ORDER BY LD030.CRS_NM

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-04 : 22:57:05

There are several errors with your current syntax. You don't seem to have a good idea what EXISTS is really for. You're also mixing the table aliases inside the EXISTS subquery with the tables outside of it. This is a really bad practice that will confuse anyone trying to follow your code logic. I tried to guess what you were trying to do. See if this works.


SELECT DISTINCT 
	LD180.STDNT_NB,
	ld010.USER_ID_CD,
	LD010.FIRST_NM,
	LD010.LAST_NM,
	LD010.HIRE_DT, 
	LD180.CRS_NB,
	LD180.CRS_VRSN_NB,
	LD030.CRS_NM, 
	LD180.ENTYCHLNGSCRVAL_PC,
	LD180.MSTRCHLNGSCRVAL_PC 
FROM 
	ZLDT180_SCR LD180 
	INNER JOIN ZLDT010_STDNT LD010 ON LD010.STDNT_NB = LD180.STDNT_NB 
	INNER JOIN ZLDT030_CRS LD030 ON LD030.CRS_NB = LD180.CRS_NB 
		AND LD030.CRS_VRSN_NB = LD180.CRS_VRSN_NB 
	INNER JOIN ZLDT075_CRRCLM_CRS LD075 ON LD075.CRS_NB = LD180.CRS_NB 
		AND LD075.CRS_VRSN_NB = LD180.CRS_VRSN_NB 
	INNER JOIN (
		SELECT DISTINCT 
			LD075.CRS_NB,
			LD075.CRS_VRSN_NB 
		FROM 
			ZLDT075_CRRCLM_CRS LD075 
			INNER JOIN ZLDT030_CRS LD030 ON LD075.CRS_NB = LD030.CRS_NB 
				AND LD075.CRS_VRSN_NB = LD030.CRS_VRSN_NB 
		WHERE 
			LD030.CRS_STAT_CD = 1 
			AND LD075.CRRCLM_CD ='1' 
			AND LD075.CRRCLM_TRACK_CD = '23' 
			AND (LD075.LOC_CD = 'BA' 
				OR LD075.LOC_CD ='ZZ')
			AND LD075.CRRCLM_CD ='1' 
			AND LD075.CRRCLM_TRACK_CD ='23' 
			AND (LD075.LOC_CD = 'BA' 
				OR LD075.LOC_CD ='ZZ')) t1 ON t1.CRS_NB = LD180.CRS_NB AND t1.CRS_VRSN_NB = LD180.CRS_VRSN_NB
WHERE
	LD010.USER_ID_CD ='tandre14 ' 
	AND LD180.QSTN_CATG_CD IN ('EC','MC') 
ORDER BY 
	LD030.CRS_NM

Get rid of the duplicate aliases, and document this code really well.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources