SQL 2008 Not Exist Query trouble.

[ITALIAORIANA]

Hi,

I am having trouble with a Not Exists query. I am trying to find all the CNS and NP rows in the same table that do not have corresponding PA rows. I mocked up data to test the query but the expectd results are not showing. It's not pulling anything. Any help would be greatly appreciated.


SELECT S.PDBC_PFX, S.SESE_ID, S.SPCT_EFF_DT, S.SPCT_TERM_DT, S.SPCT_SEQ_NO, S.SPCT_PSCD_LOW, S.SPCT_PSCD_HIGH, S.PRPR_MCTR_TYPE, S.SPCT_SESE_ID
FROM CMC_SPCT_SUPP_CONV WHERE S..SPCT_PSCD_LOW + S.SPCT_PSCD_HIGH = '5253'
AND S.SESE_ID ='WBOS'
AND S.PRPR_MCTR_TYPE IN ('NP','CNS')
AND NOT EXISTS
(SELET * FROM CMC_SPCT_SUPP_CONV P
WHERE P.SESE_ID = P.SESE_ID
AND P.PRPR_MCTR_TYPE ='PA')

Thanks
Deana

 

[gk53]

try that
I did not tested, but it should work

SELECT S.PDBC_PFX, 
		S.SESE_ID, 
		S.SPCT_EFF_DT, 
		S.SPCT_TERM_DT, 
		S.SPCT_SEQ_NO, 
		S.SPCT_PSCD_LOW, 
		S.SPCT_PSCD_HIGH, 
		S.PRPR_MCTR_TYPE, 
		S.SPCT_SESE_ID
	FROM CMC_SPCT_SUPP_CONV S
		left join CMC_SPCT_SUPP_CONV P
		on  S.SESE_ID = P.SESE_ID
		and P.PRPR_MCTR_TYPE ='PA'	
		WHERE S.SPCT_PSCD_LOW + S.SPCT_PSCD_HIGH = '5253'
		AND S.SESE_ID ='WBOS'
		AND S.PRPR_MCTR_TYPE IN ('NP','CNS')
		AND P.SESE_ID is null

 

[ITALIAORIANA]

Hi Gk53,

This did not pull anything.

Deana

 

[ITALIAORIANA]

Here is the mocked data in comma delimited. The top section is the mocked data in the system. These two values should be showing up with the query so I know to add the "PA" row as shown in the bottom section.



PDBC_PFX, SESE_ID, SPCT_EFF_DT, SPCT_TERM_DT, SPCT_SEQ_NO, SPCT_PSCD_LOW, SPCT_PSCD_HIGH, PRPR_MCTR_TYPE, SPCT_SESE_ID
MC01, WBOS, 1/1/2007 0:00, 12/31/2010 0:00, 5, 52, 53, CNS, NWBF
MC01, WBOS, 1/1/2007 0:00, 12/31/2010 0:00, 14, 52, 53, NP, NWBF



PDBC_PFX, SESE_ID, SPCT_EFF_DT, SPCT_TERM_DT, SPCT_SEQ_NO, SPCT_PSCD_LOW, SPCT_PSCD_HIGH, PRPR_MCTR_TYPE, SPCT_SESE_ID
MC01, WBOS, 1/1/2007 0:00, 12/31/2010 0:00, 5, 52, 53, CNS, NWBF
MC01, WBOS, 1/1/2007 0:00, 12/31/2010 0:00, 14, 52, 53, NP, NWBF
MC01, WBOS, 1/1/2007 0:00, 12/31/2010 0:00, 37, 52, 53, PA, PWBP

 

[gk53]

you linked your exclude selection by SESE_ID, but it is the same value for all records... and it is why no results and did you define SPCT_PSCD_LOW and SPCT_PSCD_HIGH as char or varchar?

 

[ITALIAORIANA]

The high and low are char. I see what you mean about the sese_id, because the PA won't exist so I can't do it that way.

 

[gk53]

yes it should be unique combination which linked PA and not PA records...