> Hi,
>
> I have a query that will show results from three tables:
>
> select num_abs, num_ref, registro,
> paciente.nome as pac_nome, nasc, paciente.data, data_lancado,
> unidade.nome as unid_nome, mne, cor_padrao,
> sum(case when laudado = 'S' then 1 else 0 end),
> sum(case when rotina = 'P' then 1 else 0 end),
> sum(case when laudado = 'N' then 1 else 0 end)
> From paciente, unidade, paciente_laudo
> Where paciente.unidade = unidade.mne
> AND paciente.num_ref =  paciente_laudo.num_abs_pac
> AND paciente.data between '2005-09-06' AND '2005-10-15'
> GROUP by num_abs ORDER by data DESC, registro DESC
>
> It works fine and fast. But if there are no matching records in
> 'paciente_laudo' (all 'SUM' works with paciente_laudo) this record will not
> be shown.
>
> So I made a change, and added a left join:
>
> select num_abs, num_ref, registro,
> paciente.nome as pac_nome, nasc, paciente.data, data_lancado,
> unidade.nome as unid_nome, mne, cor_padrao,
> sum(case when laudado = 'S' then 1 else 0 end),
> sum(case when rotina = 'P' then 1 else 0 end),
> sum(case when laudado = 'N' then 1 else 0 end)
> From paciente, unidade
> left join paciente_laudo on num_ref = num_abs_pac
> Where paciente.unidade = unidade.mne
> AND paciente.data between '2005-09-06' AND '2005-10-15'
> GROUP by num_abs ORDER by data DESC, registro DESC
>
> Now it works fine, but takes almost 10 times longer to run...
>
> Is there any other way of doing it?
>
>
> Thanks,
>
>
> Luiz Horacio

"Gijs Beukenoot" <Gijs.DOT.Beukenoot@A*@Wanadoo.DOT.nl> escreveu na mensagem
news:mn.83307d5a684e3dcd.23024@Wanadoo.DOT.nl...
> From Luiz Horacio :
> > Hi,
> >
> > I have a query that will show results from three tables:
> >
> > select num_abs, num_ref, registro,
> > paciente.nome as pac_nome, nasc, paciente.data, data_lancado,
> > unidade.nome as unid_nome, mne, cor_padrao,
> > sum(case when laudado = 'S' then 1 else 0 end),
> > sum(case when rotina = 'P' then 1 else 0 end),
> > sum(case when laudado = 'N' then 1 else 0 end)
> > From paciente, unidade, paciente_laudo
> > Where paciente.unidade = unidade.mne
> > AND paciente.num_ref =  paciente_laudo.num_abs_pac
> > AND paciente.data between '2005-09-06' AND '2005-10-15'
> > GROUP by num_abs ORDER by data DESC, registro DESC
> >
> > It works fine and fast. But if there are no matching records in
> > 'paciente_laudo' (all 'SUM' works with paciente_laudo) this record will
not
> > be shown.
> >
> > So I made a change, and added a left join:
> >
> > select num_abs, num_ref, registro,
> > paciente.nome as pac_nome, nasc, paciente.data, data_lancado,
> > unidade.nome as unid_nome, mne, cor_padrao,
> > sum(case when laudado = 'S' then 1 else 0 end),
> > sum(case when rotina = 'P' then 1 else 0 end),
> > sum(case when laudado = 'N' then 1 else 0 end)
> > From paciente, unidade
> > left join paciente_laudo on num_ref = num_abs_pac
> > Where paciente.unidade = unidade.mne
> > AND paciente.data between '2005-09-06' AND '2005-10-15'
> > GROUP by num_abs ORDER by data DESC, registro DESC
> >
> > Now it works fine, but takes almost 10 times longer to run...
> >
> > Is there any other way of doing it?
> >
> >
> > Thanks,
> >
> >
> > Luiz Horacio
>
> You might try adding an index to the fields that you're joining
>
>

"Jeff Johnson [MVP: VB]" <i.get@enough.spam> escreveu na mensagem
news:O$WMUvn0FHA.1252@TK2MSFTNGP09.phx.gbl...
>
> "Luiz Horacio" <lhora***@iname.com> wrote in message
> news:e4dmH8f0FHA.4032@TK2MSFTNGP15.phx.gbl...
>
> > I have a query that will show results from three tables:
>
> This is purely an SQL question, and as such it belongs in a group like
> microsoft.public.sqlserver.programming.
>
>