Query to return fields left join

I have this sql query and worked until now. Now i have a new situation. I need return all records from movccclientes but i need exclude records in Header table( CabDocCCclientes), and detail table (DetDocCCclientes) where a field called "status" is set to "A".
 SELECT MovCCClientes.Entidade, MovCCClientes.DataDocumento, MovCCClientes.NumDocumento, MovCCClientes.Descritivo, MovCCClientes.Debito, MovCCClientes.Credito,    MovCCClientes.Sinal, MovCCClientes.DataVencimento, MovCCClientes.Cotacao, MovCCClientes.Moeda, MovCCClientes.TipoEntidade, MovCCClientes.Idstamp,    CabDocCCclientes.NumDocFiscal AS NumRecibo, CabDocCCclientes.DataDocFiscal AS datarecibo, CabDocCCclientes.NomeDocumento,    DetDocCCclientes.Debito AS DebitoDetalhe, DetDocCCclientes.Credito AS CreditoDetalhe, Clientes.Nome, Clientes.Morada, Clientes.Localidade,    Clientes.CodigoPostal, Clientes.Telefone
FROM MovCCClientes
left JOIN Clientes ON Clientes.CodigoCliente = MovCCClientes.entidade
left JOIN DetDocCCclientes ON MovCCClientes.IdStamp = DetDocCCclientes.IdStampDoc    left JOIN CabDocCCclientes ON  DetDocCCclientes.IdStampCab = CabDocCCclientes.Idstamp where  MovCCClientes.DataDocumento >='2013-04-01'  and  MovCCClientes.DataDocumento <='2013-09-25'  and  MovCCClientes.entidade >=1101 and  MovCCClientes.entidade <=1101 and MovCCClientes.debito <> MovCCClientes.credito   order by MovCCClientes.Entidade,MovCCClientes.datadocumento 

Open in new window

rflorencioAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony303Commented:
In the where clause I have added an AND and an OR with brackets.

 SELECT MovCCClientes.Entidade, MovCCClientes.DataDocumento, MovCCClientes.NumDocumento, 
 MovCCClientes.Descritivo, MovCCClientes.Debito, MovCCClientes.Credito,    
 MovCCClientes.Sinal, MovCCClientes.DataVencimento, MovCCClientes.Cotacao, 
 MovCCClientes.Moeda, MovCCClientes.TipoEntidade, MovCCClientes.Idstamp,    
 CabDocCCclientes.NumDocFiscal AS NumRecibo, CabDocCCclientes.DataDocFiscal AS datarecibo,
 CabDocCCclientes.NomeDocumento,    DetDocCCclientes.Debito AS DebitoDetalhe, 
 DetDocCCclientes.Credito AS CreditoDetalhe, Clientes.Nome, Clientes.Morada, Clientes.Localidade,    
 Clientes.CodigoPostal, Clientes.Telefone
FROM MovCCClientes
left JOIN Clientes ON Clientes.CodigoCliente = MovCCClientes.entidade
left JOIN DetDocCCclientes ON MovCCClientes.IdStamp = DetDocCCclientes.IdStampDoc    
left JOIN CabDocCCclientes ON  DetDocCCclientes.IdStampCab = CabDocCCclientes.Idstamp 
where  MovCCClientes.DataDocumento >='2013-04-01'  
and  MovCCClientes.DataDocumento <='2013-09-25'  
and  MovCCClientes.entidade >=1101 
and  MovCCClientes.entidade <=1101 
and MovCCClientes.debito <> MovCCClientes.credito 
AND  (CabDocCCclientes.Status <> 'A'
OR  DetDocCCclientes.Status <> 'A')
order by MovCCClientes.Entidade,MovCCClientes.datadocumento 

Open in new window

0
rflorencioAuthor Commented:
Hi tony 303,

With your query if i have a record in MovCCClientes, but none in CabDocCCclientes and DetDocCCclientes, is not shown. In practice i need a record from each line in DetDocCCclientes,  except if status is set to "A". But if no records in DetDocCCclientes i need return also one line of data in MovCCClientes (with some information in Clientes table also).
0
SharathData EngineerCommented:
Can you try this?
SELECT MovCCClientes.Entidade, 
       MovCCClientes.DataDocumento, 
       MovCCClientes.NumDocumento, 
       MovCCClientes.Descritivo, 
       MovCCClientes.Debito, 
       MovCCClientes.Credito, 
       MovCCClientes.Sinal, 
       MovCCClientes.DataVencimento, 
       MovCCClientes.Cotacao, 
       MovCCClientes.Moeda, 
       MovCCClientes.TipoEntidade, 
       MovCCClientes.Idstamp, 
       CabDocCCclientes.NumDocFiscal  AS NumRecibo, 
       CabDocCCclientes.DataDocFiscal AS datarecibo, 
       CabDocCCclientes.NomeDocumento, 
       DetDocCCclientes.Debito        AS DebitoDetalhe, 
       DetDocCCclientes.Credito       AS CreditoDetalhe, 
       Clientes.Nome, 
       Clientes.Morada, 
       Clientes.Localidade, 
       Clientes.CodigoPostal, 
       Clientes.Telefone 
  FROM MovCCClientes 
       LEFT JOIN Clientes 
              ON Clientes.CodigoCliente = MovCCClientes.entidade 
       LEFT JOIN DetDocCCclientes 
              ON MovCCClientes.IdStamp = DetDocCCclientes.IdStampDoc AND DetDocCCclientes.Status = 'A'
       LEFT JOIN CabDocCCclientes 
              ON DetDocCCclientes.IdStampCab = CabDocCCclientes.Idstamp AND CabDocCCclientes.Status = 'A'
 WHERE MovCCClientes.DataDocumento >= '2013-04-01' 
   AND MovCCClientes.DataDocumento <= '2013-09-25' 
   AND MovCCClientes.entidade >= 1101 
   AND MovCCClientes.entidade <= 1101 
   AND MovCCClientes.debito <> MovCCClientes.credito 
 ORDER BY MovCCClientes.Entidade, 
          MovCCClientes.datadocumento

Open in new window

If you still not getting what you are looking for, post the result of your original query and expected result.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
>> I need return all records from movccclientes
from movccclientes , left joining others (tick)

>> but i need exclude records ... CabDocCCclientes and ... DetDocCCclientes
these require a JOIN CONDITION (not a where condition)

>>  where a field called "status" is set to "A".
to exclude, the the JOIN CONDITION would be:

AND [table|alias].status <> 'A' -- (which you have in the where clause, not in the joins)

So, I agree with Sharath's immediately above except for: <> 'A'

@Sharath, how are you getting to all these so fast?
0
rflorencioAuthor Commented:
thanks a lot, great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.