Problems with SELECT RIGHT JOIN

Question

Problems with SELECT RIGHT JOIN

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (1 votes)

2

I have two tables, A and B. In Table A, I have a column with machine names (Cortadeira, Baler, Packer and Rewinder) and in the other column some causes that made them stop Lack of Electric Power, Coil Replacement, etc).

In Table B , I have a column with the list of all possible reasons (In Table A there are only those that have already occurred, each row shows the name of one machine and the reason for your stop.)

Goal:

Have the code return me the number of times a particular machine has stopped for each reason, returning the value 0 if it has not 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 Cortadeira has stopped for a reason X and returns NULL when no machine stopped for some reason. The problem is that when the LOCKER did not stop for a reason Y but another MACHINE stopped, it just does not return any value to me when I needed it to return 0/NULL . What can I do to change this?

Tables:

This is a Table A pa_maquina refers to the Question machines column and pa_motivos refers to the MotivosQueAconteceram column:

ThisisTableB,columndescricaoreferstoTodosOsMotivos:

mysql sql join sql-select

asked by anonymous 07.03.2018 / 12:29

3 answers

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

07.03.2018 / 13:58

1

Another form of solution:

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

Example online: 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

07.03.2018 / 14:37

Changing variable value Extract portion of characters using PHP

score 3 · Accepted Answer

As you want to sort by the B table and then restrict by the A table, I suggest that 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

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