Help SQL Sentence (intersect select)

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

Transact-SQL (2000)

Help SQL Sentence (intersect select)

Author

Topic

jalejandro0211
Starting Member

9 Posts

Posted - 2006-11-19 : 20:53:15

Hi, I have this select

SELECT CEDULA, COUNT(CEDULA) AS CONT FROM MATRIX_ODONTOGRAMA GROUP BY CEDULA HAVING CONT = 3

this return

111111
222222
333333
444444
555555

AND THIS:

SELECT matrix_odontograma.cedula as Cedula, primer_nombre as Nombre, primer_apellido as Apellido, genero as Genero, fecha_nacimiento as FechaNacimiento FROM matrix_odontograma, pacientes where (matrix_odontograma.cedula = pacientes.cedula) AND (idDientes = 1 AND Superficie = 1 AND nombre_enfermedad = 'Caries' or idDientes = 2 AND Superficie = 6 AND nombre_enfermedad = 'PPR' or idDientes = 3 AND Superficie = 6 AND nombre_enfermedad = 'Extraido' ) group by cedula having count(matrix_odontograma.cedula) between 3 and 3

THIS RETURN

666666
333333
555555
222222
777777

I NEED INTERSECT WITH JOIN? INNER JOIN? SUBSELECT? OR SOMETHING TO RETURN

222222
333333
555555
(THE VALUES THAT HAVE THE FIRST SELECT AND SECOND SELECT IN COMMON)

TKS 4 U HELP.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-19 : 23:21:01

You need a simple join:

Select Tbl1.Cedula
From
(SELECT CEDULA, COUNT(CEDULA) AS CONT 
FROM MATRIX_ODONTOGRAMA 
GROUP BY CEDULA HAVING CONT = 3) as Tbl1
Join
(SELECT matrix_odontograma.cedula as Cedula, primer_nombre as Nombre,
primer_apellido as Apellido, genero as Genero, 
fecha_nacimiento as FechaNacimiento 
FROM matrix_odontograma, pacientes 
where (matrix_odontograma.cedula = pacientes.cedula) AND
(idDientes = 1 AND Superficie = 1 AND nombre_enfermedad = 'Caries'
or idDientes = 2 AND Superficie = 6 AND nombre_enfermedad = 'PPR' 
or idDientes = 3 AND Superficie = 6 AND nombre_enfermedad = 'Extraido' ) 
group by cedula 
having count(matrix_odontograma.cedula) between 3 and 3) as tbl2
on tbl1.Cedula = tbl2.Cedula

Also, read below article to know how to mimic INTERSECT and MINUS operator in SQL Server:

[url]http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm[/url]

Harsh Athalye
India.
"Nothing is Impossible"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 00:26:48

You'll be pleased to know that SQL Server 2005 now supports the INTERSECT and EXCEPT operators.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 02:58:41

quote:
You'll be pleased to know that SQL Server 2005 now supports the INTERSECT and EXCEPT operators.

Yeah...that's good! Firstly, we don't have to reinvent the wheel and secondly, it will be faster than any other indirect approach.

Harsh Athalye
India.
"Nothing is Impossible"

jalejandro0211
Starting Member

9 Posts

Posted - 2006-11-20 : 10:56:06

quote:

Originally posted by harsh_athalye

You need a simple join:

Select Tbl1.Cedula
From
(SELECT CEDULA, COUNT(CEDULA) AS CONT 
FROM MATRIX_ODONTOGRAMA 
GROUP BY CEDULA HAVING CONT = 3) as Tbl1
Join
(SELECT matrix_odontograma.cedula as Cedula, primer_nombre as Nombre,
primer_apellido as Apellido, genero as Genero, 
fecha_nacimiento as FechaNacimiento 
FROM matrix_odontograma, pacientes 
where (matrix_odontograma.cedula = pacientes.cedula) AND
(idDientes = 1 AND Superficie = 1 AND nombre_enfermedad = 'Caries'
or idDientes = 2 AND Superficie = 6 AND nombre_enfermedad = 'PPR' 
or idDientes = 3 AND Superficie = 6 AND nombre_enfermedad = 'Extraido' ) 
group by cedula 
having count(matrix_odontograma.cedula) between 3 and 3) as tbl2
on tbl1.Cedula = tbl2.Cedula

jalejandro0211
Starting Member

9 Posts

Posted - 2006-11-20 : 10:58:46

Tks Harsh 4 ur help...

Works Excellent....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources