Problems with SELECT RIGHT JOIN

Asked

Viewed 86 times

2

I have two tables, A and B. Na Table A, I have a column with names of machines (Cutter, Baler, Packer and Rewinder) and in the other column some causes that made them stop (As Lack of Electrical Power, Coil Exchange, etc).

In the Table B, I have a column listing all possible reasons (In Table A are just the ones that already occurred, each line shows the name of a machine and the reason for its stop.)

Goal:

Make the code return me the number of times a specific machine has been stopped for each reason, returning the value 0 if she hasn’t been stopped by that.

Current query:

SELECT 
    B.TodosOsMotivos, 
    A.MotivosQueAconteceram, 
    A.Maquinas, 
    COUNT(A.MotivosQueAconteceram) as NumeroDeOcorrencias 
FROM A 
RIGHT join B on A.MotivosQueAconteceram = B.TodosOsMotivos 
WHERE A.Maquinas = 'Cortadeira' 
OR A.Maquinas IS NULL 
GROUP by MotivosQueAconteceram, Maquinas, B.TodosOsMotivos 
order by B.TodosOsMotivos

That way she tells me how many times the Cutter stood still for an X reason and returns NULL when no machine has stopped for any reason. The problem is that when the CUTTER didn’t stop for a Y reason but ANOTHER MACHINE stopped, it just doesn’t return any value to me, when I needed it to return to me 0/NULL. What can I do to change that?

Tables:

That’s the Table A pa_maquina refers to the Question Machines column and pa_motivos refers to the column MotivosQueAconteceram: inserir a descrição da imagem aqui

That’s the Table B, the column descricao refers to the TodosOsMotivos:

inserir a descrição da imagem aqui

mysql sql database join select-sql

  • put the structure of the tables please, it is kind of strange that, the data are not normalized ?

  • So just to understand. Do you want the number of times the CUTTER has been stopped and, if the machine is different, that I appear 0? It doesn’t make much sense, it’s not better to show the amount independent of the machine type?

  • Sorack, I need to form a chart for the cutter informing her stop index for each motif. Later it will be made individually for each machine. It turns out that when a reason happened to another machine and not to the one where I’m doing the graph, it doesn’t return 0 times when it stopped for that reason, it returns nothing, and so php doesn’t include that reason in the graph. So I would need some return value, even if 0.

  • dvd, when used COUNT and do not use GROUP by gives error.

3 answers

3


How you want to separate for table reasons B and then restrict by the table A, I suggest you make a subselect as follows:

SELECT b.TodosOsMotivos,
       (SELECT COUNT(1)
          FROM A a
         WHERE a.MotivosQueAconteceram = b.TodosOsMotivos
           AND a.Maquinas = 'Cortadeira'
         GROUP BY a.MotivosQueAconteceram,
                  a.Maquinas) AS NumeroDeOcorrencias
  FROM B b

Thus all reasons will be listed and, within each, the number of occurrences in the table A for the column Maquinas with value of Cortadeira will be counted correctly.

1

I suggest the following SELECT:

SELECT
    B.Maquina,
    B.Motivo,
    SUM(B.Total)
FROM
    (SELECT
        CASE
            WHEN B.[descricao] = A.[pa_motivo]
            THEN 1 ELSE 0
            END AS 'Total',
        A.[pa_maquina] AS 'Maquina',
        B.[descricao] AS 'Motivo'
    FROM
        #pa_maquina A, #pa_motivos B) B
GROUP BY
    B.Maquina,
    B.Motivo
ORDER BY
    B.Maquina,
    B.Motivo

1

Another form of solution:

You make a query that returns the union of all machine combinations and motif with zero amount (CROSS JOIN) and all machine combinations and reason that have some stopping(INNER JOIN) and this query you return the records with the largest amount and can filter by the machine you want.

Online example: Sqlfiddle

SELECT Nome
  , Motivo
  , MAX(Quantidade) AS 'Quantidade de paradas'
FROM 
  (SELECT
    pa_maquina.pa_maquina AS Nome
    , pa_motivo.Descricao AS Motivo
    , 0 AS Quantidade
  FROM pa_maquina
  CROSS JOIN pa_motivo
  UNION
  SELECT
    pa_maquina.pa_maquina AS Nome
    , pa_motivo.Descricao AS Motivo
    , COUNT(pa_motivo.id) AS Quantidade
  FROM pa_maquina
  JOIN pa_motivo 
    ON pa_motivo.id = pa_maquina.pa_motivo
  GROUP BY Nome, Motivo
) AS Uniao
WHERE Nome = 'Cortadeira'
GROUP BY Nome, Motivo

inserir a descrição da imagem aqui

Browser other questions tagged mysql sql database join select-sql

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