ayuda con DISTINCT+LEFT JOIN - 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

Transact-SQL (2000)

ayuda con DISTINCT+LEFT JOIN

Author

Topic

lau
Starting Member

3 Posts

Posted - 2004-08-06 : 12:08:27

Necesito unir 3 tablas:
laboratorios (idlaboratorio, is_Caeme)
formularios_medicamento (idformulario, idlaboratorio)
formularios (idformulario)

yo quiero obtener todos los campos dentro de formulario donde el laboratorio de ese formulario sea is_caeme=0

el query que yo hice -->
select f.* from formularios f left join formularios_medicamentos fm on fm.idformulario=f.idformulario left join laboratorios l on l.idlaboratorio=fm.idlaboratorio where l.is_caeme=0 order by f.idformulario <--

me devuelve tanta cantidad de registros como medicamentos haya en la tabla de formularios_medicamento
y yo solo quiero un registro por idformulario
espero que me puedan ayudar
ya que es el ultimo problema q tengo y hoy entrego el proyecto
muchas gracias
laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-06 : 13:10:28

Could you translate to English?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-06 : 13:20:22

here's what Google told me:

I need to unite 3 tables:
laboratories (idlaboratorio, is_Caeme)
formularios_medicamento (idformulario, idlaboratorio)
forms (idformulario)

I want to obtain all the fields within form where the laboratory of that form is is_caeme=0

query that I made -->
select f. * from forms f left join formularios_medicamentos fm on fm.idformulario=f.idformulario left join laboratories l on l.idlaboratorio=fm.idlaboratorio where l.is_caeme=0 order by f.idformulario < - -

gives back as much amount to me of registries as medicines have in the formularios_medicamento table and single
I want a registry by idformulario
I hope that they can help me since she is I complete problem q
I have and today I give the project thank you very much

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-06 : 13:27:09

I need to combine 3 tables:

labororatorios (idlaboratorio, is_Caeme)
formularios_medicamento (idformulario, idlaboratorio)
formularios (idformulario)

I want to obtain all the fields within form where the laboratory of that formula is is_caeme=0

The query that I made -->


select 
 f. * 
from 
 formularios f 
 left join formularios_medicamentos fm on fm.idformulario=f.idformulario 
 left join labororatorios l on l.idlaboratorio=fm.idlaboratorio 
where 
 l.is_caeme=0 
order by 
 f.idformulario

gives back the registry (amount) of medicines in the formularios_medicamento table

I only want a registry by idformulario.

I hope that you can help me since I'm supposed to complete the project and was just assigned it today.

I think this is close to the right translation anyway. Hope it helps.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-06 : 13:28:00

I came up with the same query as the poster except I had inner joins.

So what does your query return you now and what is it supposed to return you?

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-06 : 13:33:28

Justificar esto:

SELECT f.* 
   FROM forms f
   WHERE EXISTS (SELECT *
      FROM formularios_medicamentos fm
      INNER JOIN laboratories l on l.idlaboratorio=fm.idlaboratorio
      WHERE  fm.idformulario = f.idformulario 
         AND l.is_caeme = 0)
   ORDER BY f.idformulario

-PatP

lau
Starting Member

3 Posts

Posted - 2004-08-06 : 13:35:01

now it is returning me as many registries as the amount of registries that i've got in the formularios_medicamentos table
because of that the query returns me the same registries many times
and i just want then once per idformulario

lau
Starting Member

3 Posts

Posted - 2004-08-06 : 13:50:28

thanks to all of you
the post from Pat Phelan really helps me
but i changed a little

i am posting it if somebody is interested...:
SELECT *
FROM
formularios res
LEFT JOIN formularios_tipo ft
ON res.idtipo=ft.idtipo
,
(SELECT DISTINCT(f.idformulario),MIN(desc_laboratorio) AS descrip
FROM
formularios f
LEFT JOIN formularios_medicamentos fm
ON f.idformulario=fm.idformulario
LEFT JOIN medicamentos m
ON m.idmedicamento=fm.idmedicamento
LEFT JOIN laboratorios l
ON m.idlaboratorio=l.idlaboratorio
LEFT JOIN medicos med
ON med.idmedico=f.idmedico
GROUP BY f.idformulario) AS res2
WHERE res.idformulario=res2.idformulario

again thank to all of you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources