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
 General SQL Server Forums
 New to SQL Server Programming
 Please help me to write down a query

Author  Topic 

andrewbale
Starting Member

1 Post

Posted - 2014-08-27 : 04:26:48
Hi everybody. I don't know english very well so I'm sorry if I make some mistakes. And I'm sorry if this is not the right place to make this question, but I can't find a dedicated section where people can ask for this kind of help.

Let's start:

I have this query
SELECT count(*)
FROM sispc.funghi.consulenza_pronto_soccorso as c
JOIN sispc.gaa.agente as ag ON c.autore = ag.id
JOIN sispc.ana.pf_persona as pf1 ON pf1.id = ag.persona_id
JOIN sispc.anapratica.pratica as p ON p.id = c.pratica_id
JOIN sispc.anapratica.pratica_anagrafica as pana ON pana.pratica_id = p.id
JOIN sispc.ana.pf_persona as pf2 ON pana.anagrafica_id = pf2.sispc_id
JOIN sispc.gaa.ao_uo_zona as uoZona ON uoZona.id = p.uo
JOIN sispc.gaa.ao_uo as uo ON uo.id = uoZona.uo_id
JOIN sispc.gaa.zona as zona ON zona.id = uoZona.zona_id
JOIN sispc.anapratica.pratica_assegnatario as pa ON p.id = pa.pratica_id
JOIN sispc.gaa.agente as a ON a.id = pa.agente_id
JOIN sispc.ana.pf_persona as pf ON pf.id = a.persona_id

that returns 33 (i want 13) because of the last but two JOIN (there are more than one record in pa (pratica_assegnatario) that join p (pratica)).
I need that that JOIN references only one record in pratica_assegnatario (pa), the one with the max id!
Do I need a nested query?
I don't know if you understand my problem, but I hope so and I hope you can help me. I am a newbie so sorry if this question is too stupid :)
Bye, thank you.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-27 : 08:33:51
Try replacing this line:


JOIN sispc.anapratica.pratica_assegnatario as pa ON p.id = pa.pratica_id


with something like this:


JOIN (select pratica_id, max(id) as id
from sispc.anapratica.pratica_assegnatario
group by pratica_id) pa
ON p.id = pa.pratica_id
Go to Top of Page
   

- Advertisement -