SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Please help me to write down a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andrewbale
Starting Member

1 Posts

Posted - 08/27/2014 :  04:26:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1776 Posts

Posted - 08/27/2014 :  08:33:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000