Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

2780 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  
 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.05 seconds. Powered By: Snitz Forums 2000