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.
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:
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
Advertisement
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