Please help me to write down a query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources