SQL MIXING EVERYTHING

Asked

Viewed 114 times

-1

SQL MISTURANDO TUDO I have the following query above as per the image.

Why is she mixing the fields?

SELECT 
    celulasreunioes.idCelula, 
    celulas.nome,
    celulas.idRede,
    redes.nome,
    celulas.idRegiao,
    regioes.nome,
    celulas.idArea,
    areas.nome,
    celulas.idSetor,
    setores.nome,                       
    celulasreunioes.data, 
    celulasreunioes.presentes,
    celulasreunioes.visitas,
    celulasreuniOes.criancas,
    celulasreunioes.decisoes,
    celulasreunioes.discipulados,
    celulasreunioes.jejum,
    celulasreunioes.evangelismo,
    celulasreunioes.supervisao,
    celulasreunioes.oferta
FROM celulasreunioes, pastores, redes, regioes, areas, setores, celulas
WHERE
  celulas.idRede = redes.idRede AND
  celulas.idRegiao = regioes.idRegiao AND
  celulas.idArea = areas.idArea AND
  celulas.idSetor = setores.idSetor AND 
  celulas.idPastor = pastores.idPastor AND 
    celulas.idPastor = 2 AND 
    data BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
/*GROUP BY celulas.idCelula*/
ORDER BY celulas.idSetor

For example:

To Cell of 17 is the Cell 1 To Cell of 18 is the Cell 2

But the query says that Cells 17 and 18 are the same time to 1 and 2

In case I cross the line

/*GROUP BY celulas.idCelula*/

Which is the goal, the return gets even worse because omit the outworking to the Cell 18 as shown below.

inserir a descrição da imagem aqui

The expected return (with the other clauses of query) )is as in the figure below that has only the meeting table print:

inserir a descrição da imagem aqui

Following the help of Rafael Dias, and how **NÃO** there is how to post a new answer to help in the forum because they put the question as pending, I will put right here as was the query final, but already compiled

SELECT 
     celulasreunioes.idCelula, 
     celulas.nome as nomeCelula,
     pastores.idPastor,
     membros.nome as nomePastor,
     celulas.idRede,
     redes.nome as nomeRede,
     celulas.idRegiao,
     regioes.nome as nomeRegiao,
     celulas.idArea,
     areas.nome as nomeArea,
     celulas.idSetor,
     setores.nome as nomeSetor,                      
     celulasreunioes.data, 
     celulasreunioes.presentes,
     celulasreunioes.visitas,
     celulasreuniOes.criancas,
     celulasreunioes.decisoes,
     celulasreunioes.discipulados,
     celulasreunioes.jejum,
     celulasreunioes.evangelismo,
     celulasreunioes.supervisao,
     celulasreunioes.oferta
FROM
     celulasreunioes, 
     pastores, 
     redes, 
     regioes, 
     areas, 
     setores, 
     celulas, 
     membros
WHERE
     celulas.idRede = redes.idRede AND
     celulas.idRegiao = regioes.idRegiao AND
     celulas.idArea = areas.idArea AND
     celulas.idSetor = setores.idSetor AND 
     celulas.idCelula = celulasreunioes.idCelula AND 
     celulas.idPastor = pastores.idPastor AND 
     pastores.idPastor = membros.idMembro AND 
     data BETWEEN NOW() - INTERVAL 7 DAY AND NOW() 
ORDER BY 
    celulas.nome, 
    membros.nome,
    redes.nome, 
    regioes.nome, 
    areas.nome, 
    setores.nome
mysql sql

  • And what are the data present in the table? What would be the expected result?

  • added to the question!

  • also adds a cell select equal to 17 and 18. Just to make sure your table has not been duplicated.

  • I can not because there will be more than 300 cells, in this moment of development I only registered these 2. Or I did not understand your guidance.

  • select * form cellulasesWhere meetings idCellular = 17

  • 1

    So you’re saying that there’s only two records in the table? You can create a [mcve] using https://www.db-fiddle.com/?

  • 1

    The query is running perfectly. If you want a result different than expected, please [Dit] the question and put a demonstration of the expected result and an explanation of why you think it should be different from the one presented. The way it is, you can’t know where the logic error is (the answer may be a solution, but only knowing for sure what the original intention is)

Show 2 more comments

1 answer

3


Your JOINS are not working properly and as it is, there is a CROSS JOIN between celulas and celulasreunioes - that is, all values of celulasreunioes will appear repeated.

Note that in the WHERE clause the table celulasreunioes does not appear. It has to be linked to other.

FROM celulasreunioes, pastores, redes, regioes, areas, setores, celulas
WHERE
  celulas.idRede = redes.idRede AND
  celulas.idRegiao = regioes.idRegiao AND
  celulas.idArea = areas.idArea AND
  celulas.idSetor = setores.idSetor AND 
  celulas.idPastor = pastores.idPastor AND 
    celulas.idPastor = 2 AND 
    data BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

One advantage of using the INNER Joins syntax of the ANSI standard is that it simplifies picking up these details. For example, turning the above WHERE into Joins the problem becomes apparent:

FROM celulas
  INNER JOIN pastores ON celulas.idPastor = pastores.idPastor
  INNER JOIN redes ON celulas.idRede = redes.idRede
  INNER JOIN regioes ON celulas.idRegiao = regioes.idRegiao
  INNER JOIN areas ON celulas.idArea = areas.idArea
  INNER JOIN setores ON celulas.idSetor = setores.idSetor
  --FALTOU o celulasreunioes
WHERE
    celulas.idPastor = 2 AND 
    data BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
  • 1

    thank you, final query, at the end of the question

Browser other questions tagged mysql sql

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