Select how many documents are in other table for each person

I have 3 tables.

Client,Documents and ClientDocuments. The first one is the clients,where the information for each client are. The second one is the documents, which document can go in the system. The third one is the ClientDocuments, which client has which document.

Tables Here

I have to do a select where i get the information from the clients, and how many documents of the 3 types of the documents they have. Example,Client 1 Have one document called ‘Contrato Social’,and 2 called ‘Ata de Negociação’. In the select must return every client and in the columns ContratoSocial returns 1,Ata Negociacao returns 2 and Aceite de Condições Gerais returns 0.

I did this to show.

select idFornecedor,
txtNomeResumido,
txtNomeCompleto,
--txtEmail,
--txtSenha,
bitHabilitado,
(SELECT Count(idDocumentosFornecedoresTitulo) FROM tbDocumentosFornecedores WHERE idDocumentosFornecedoresTitulo = 1) AS 'Contrato Social',
(SELECT Count(idDocumentosFornecedoresTitulo) FROM tbDocumentosFornecedores WHERE idDocumentosFornecedoresTitulo = 2) AS 'Ata de Negociação',
(SELECT Count(idDocumentosFornecedoresTitulo) FROM tbDocumentosFornecedores WHERE idDocumentosFornecedoresTitulo = 3) AS 'Aceite de Condições Gerais'
from dbo.tbFornecedores tbf
order by tbf.txtNomeResumido asc

returns this:

Returns of the query

But its just counting how many documents from that type is in the database, i want to filter for each client, how should i do?

Working answer:

select tbf.idFornecedor, tbf.txtNomeResumido, tbf.txtNomeCompleto,
tbf.bitHabilitado,
       sum(case when idDocumentosFornecedoresTitulo = 1 then 1 else 0 end) as contrato_social,
       sum(case when idDocumentosFornecedoresTitulo = 2 then 1 else 0 end) as Ata_de_Negociação,
       sum(case when idDocumentosFornecedoresTitulo = 3 then 1 else 0 end) as Aceite_de_Condições_Gerais
from dbo.tbFornecedores tbf left join
     tbDocumentosFornecedores df
     on tbf.idFornecedor = df.idFornecedor 
group by tbf.idFornecedor, tbf.txtNomeResumido, tbf.txtNomeCompleto, tbf.bitHabilitado
order by tbf.txtNomeResumido asc

Answer

You need some way of matching the rows in tbDocumentosFornecedores to the rows in tbFornecedores. Your question is not clear on what column is used for that, but I might guess something like idDocumentosFornecedore.

You could fix your query by using a correlation clause. However, I might instead suggest conditional aggregation:

select tbf.idFornecedor, tbf.txtNomeResumido, tbf.txtNomeCompleto,
tbf.bitHabilitado,
       sum(case when idDocumentosFornecedoresTitulo = 1 then 1 else 0 end) as contrato_social,
       sum(case when idDocumentosFornecedoresTitulo = 2 then 1 else 0 end) as Ata_de_Negociação,
       sum(case when idDocumentosFornecedoresTitulo = 3 then 1 else 0 end) as Aceite_de_Condições_Gerais
from dbo.tbFornecedores tbf left join
     tbDocumentosFornecedores df
     on tbf.idDocumentosFornecedore = df.idDocumentosFornecedore  -- this is a guess
group by tbf.idFornecedor, tbf.txtNomeResumido, tbf.txtNomeCompleto, tbf.bitHabilitado
order by tbf.txtNomeResumido asc