Query Mysql three tables

Asked

Viewed 227 times

0

I am trying to make a query in the MYSQL database, but the query is not getting back what I wanted. I want to make a query where return all modules and modules that the company has with the clause where in Empresa_ID.

Mysql attempt:

   SELECT empresas.empresa_id
         ,empresas.empresa_razaosocial
         ,modulos.modulo_id
         ,modulos.modulo_nome
         ,empresa_modulo.modulo_id
     FROM empresa_modulo
    RIGHT JOIN modulos
       ON modulos.modulo_id = empresa_modulo.modulo_id
    RIGHT JOIN empresas
       ON empresas.empresa_id = empresa_modulo.empresa_id
   UNION
   SELECT empresas.empresa_id
         ,empresas.empresa_razaosocial
         ,modulos.modulo_id
         ,modulos.modulo_nome
         ,empresa_modulo.modulo_id
     FROM empresa_modulo
    RIGHT JOIN modulos
       ON modulos.modulo_id = empresa_modulo.modulo_id
    RIGHT JOIN empresas
       ON empresa_modulo.empresa_id = empresas.empresa_id
    WHERE empresas.empresa_id = 1;

Upshot:

inserir a descrição da imagem aqui

I did this consultation, but I was not successful. How can I perform this consultation?

A query that returns all the modules and modules that the company has.

mysql sql query
  • 2

    Without having an example of the data it is difficult to help. Apparently your query is right, it cannot be data that was not found in right join?

  • 1

    What do you want? What comes back? Explain better.

  • It’s right, right, right

  • 1

    @Joanmarcos You need to show a sample of the data, what you’re getting at the moment, and what you’d like to receive when it’s right.

  • @user94991 is the result.

1 answer

2


Joan, the result print is what you expect, right?

Your query is wrong, since you are wanting all the modules there is no need for you to use right join.

--Nesse select você vai relacionar todos os modulo que tem relacionamento.
SELECT e.empresa_id
      ,e.empresa_razaosocial
      ,m.modulo_id
      ,m.modulo_nome
      ,em.modulo_id
  FROM empresa_modulo em
  JOIN modulos m
    ON m.modulo_id = em.modulo_id
  JOIN empresas e
    ON e.empresa_id = em.empresa_id
---Se adicionar o where, você selecionará apenas a empresa com o id 1.
 WHERE e.empresa_id = 1;

You know the difference Union and Union ALL?

When you use the UNION it will return only the unique records, with the ALL you will have repeated line.

Try to use the following:

SELECT e.empresa_id
      ,e.empresa_razaosocial
      ,m.modulo_id
      ,m.modulo_nome
      ,em.modulo_id
  FROM empresa_modulo em
 CROSS JOIN modulos m
    ON m.modulo_id = em.modulo_id
 CROSS JOIN empresas e
    ON e.empresa_id = em.empresa_id;

Browser other questions tagged mysql sql query

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