How to make a group by Select is done to more than one table

Asked

Viewed 169 times

0

SELECT  
    tblOrgao.eleicao_id as IdEleicao,
    tblCargo.orgao_id as IdOrgao,
    tblOrgao.orgao as Orgao,
    tblCargo.id as IdCargo, 
    tblCargo.cargo as Cargo, 
    tblLista.lista as NomeLista,
    tblListaCandidatos.candidato_id as NumberMembro 
FROM
tblOrgao  inner join 
    tblCargo on  tblCargo.orgao_id = tblOrgao.id, 
tblListaCandidatos LEFT JOIN 
    tblLista ON tblLista.id = tblListaCandidatos.lista_id
WHERE tblOrgao.eleicao_id = '20' AND tblLista.lista = 'A'

Output inserir a descrição da imagem aqui

The member number is appearing 3 times repeated I’ve tried using one Group by tblCargo.id

I tried so 

SELECT  
    tblOrgao.eleicao_id as IdEleicao,
    tblCargo.orgao_id as IdOrgao,
    tblOrgao.orgao as Orgao,
    tblCargo.id as IdCargo, 
    tblCargo.cargo as Cargo, 
    tblLista.lista as NomeLista,
    tblListaCandidatos.candidato_id as NumberMembro 
FROM
tblOrgao  inner join 
    tblCargo on  tblCargo.orgao_id = tblOrgao.id, 
tblListaCandidatos LEFT JOIN 
    tblLista ON tblLista.id = tblListaCandidatos.lista_id
WHERE tblOrgao.eleicao_id = '20' AND tblLista.lista = 'A'
Group by tblCargo.id;

Msg 8120, Level 16, State 1, Line 2 Column 'tblOrgao.eleicao_id' is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause.

What to get is the following output

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

sql

  • After that recommendation was like this Output and either way I really need the data from tblListaCandidatos.candidato_id as Numbermembro to appear in this query

  • You could post as these doing group by. in that select there is no group by any

  • 1

    tbllistacandidates have organ id field?

  • no, you have the job id

  • Instead of just presenting the SQL command explain what you want to get.

  • Not knowing the structure of the tables becomes complicated. If you have how to post it would be clearer.

  • Note that, for example for Numbermembro=1001, there is more value for the organ field. Why should one value be chosen over another? Since you are not using any aggregation function maybe the use of the GROUP BY option is not the most suitable for your problem.

  • What should I use to get the values that way?

Show 3 more comments

2 answers

1


As you are doing a random CROSS JOIN see what the result of the command below is and, if so, what needs to improve:

SELECT  
    tblOrgao.eleicao_id as IdEleicao,
    tblCargo.orgao_id as IdOrgao,
    tblOrgao.orgao as Orgao,
    tblCargo.id as IdCargo, 
    tblCargo.cargo as Cargo, 
    tblLista.lista as NomeLista,
    tblListaCandidatos.candidato_id as NumberMembro 
FROM tblOrgao INNER JOIN tblCargo ON (tblCargo.orgao_id = tblOrgao.id)
                INNER JOIN tblListaCandidatos ON (tblCargo.id = tblListaCandidatos.cargo_id)
                INNER JOIN tblLista ON (tblListaCandidatos.lista_id = tblLista.id)
WHERE tblOrgao.eleicao_id = '20' AND tblLista.lista = 'A';
  • 1

    What are the foreign keys of all tables involved?

1

All SELECT fields that are grouping (fields that are not calculated as SUM, COUNT, etc.) must be in the GROUP BY clause

If what you need is to see only the result without repetition you can use a DISTINCT instead of a GROUP BY to get cleaner.

-- Com DISTINCT:
SELECT  DISTINCT
    tblOrgao.eleicao_id as IdEleicao,
    tblCargo.orgao_id as IdOrgao,
    tblOrgao.orgao as Orgao,
    tblCargo.id as IdCargo, 
    tblCargo.cargo as Cargo, 
    tblLista.lista as NomeLista,
    tblListaCandidatos.candidato_id as NumberMembro 
FROM tblOrgao 
inner join 
    tblCargo on  tblCargo.orgao_id = tblOrgao.id, 
tblListaCandidatos LEFT JOIN 
    tblLista ON tblLista.id = tblListaCandidatos.lista_id
WHERE tblOrgao.eleicao_id = '20' AND tblLista.lista = 'A'

-- Com GROUP BY:
SELECT tblOrgao.eleicao_id as IdEleicao,
    tblCargo.orgao_id as IdOrgao,
    tblOrgao.orgao as Orgao,
    tblCargo.id as IdCargo, 
    tblCargo.cargo as Cargo, 
    tblLista.lista as NomeLista,
    tblListaCandidatos.candidato_id as NumberMembro
FROM tblOrgao 
inner join 
    tblCargo on  tblCargo.orgao_id = tblOrgao.id, 
tblListaCandidatos LEFT JOIN 
    tblLista ON tblLista.id = tblListaCandidatos.lista_id
WHERE tblOrgao.eleicao_id = '20' AND tblLista.lista = 'A'
--É só remover os apelidos de coluna
GROUP BY tblOrgao.eleicao_id, tblCargo.orgao_id, tblOrgao.orgao,tblCargo.id, tblCargo.cargo, tblLista.lista,tblListaCandidatos.candidato_id

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.