NOT EXISTS

18/02/2008 - 20:34 por Penta | Informe spam
Estimados.
Utilizo SQL Server 2000 SP4

Tengo la siguiente consulta:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and a.rut collate Modern_Spanish_CI_AS NOT IN (SELECT substring(nick,
1,len(nick)-2) collate Modern_Spanish_CI_AS rut_alumno FROM TABLA8
T)
Pues bien esta consulta me devuelve N registros.

Si cambio el NOT IN por NOT EXISTS:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and NOT exists (SELECT * FROM TABLA8 T)

Aca NO me devuelve registros.

Agradeceria sus comentarios.
Atte.
Penta.
 

Leer las respuestas

#1 Alejandro Mesa
18/02/2008 - 20:49 | Informe spam
Trata:

...
and not exists (
select *
from TABAL8 AS T
where
substring(T.nick, 1,len(T.nick)-2) collate Modern_Spanish_CI_AS =
a.rut collate Modern_Spanish_CI_AS
)


AMB

"Penta" wrote:

Estimados.
Utilizo SQL Server 2000 SP4

Tengo la siguiente consulta:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and a.rut collate Modern_Spanish_CI_AS NOT IN (SELECT substring(nick,
1,len(nick)-2) collate Modern_Spanish_CI_AS rut_alumno FROM TABLA8
T)
Pues bien esta consulta me devuelve N registros.

Si cambio el NOT IN por NOT EXISTS:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and NOT exists (SELECT * FROM TABLA8 T)

Aca NO me devuelve registros.

Agradeceria sus comentarios.
Atte.
Penta.

Preguntas similares