Given two groups and processes tables. Where each group can have 0 or N processes. What is the best way to get this cross.
Grupo
--------
id
Nome
bol_ativo = S
Processo
-------
id
numero
ativo = S
encaminhado = N
What I would wish would be the number of active and not forwarded processes of each active group. If the group does not have any processes, it should appear as 0 in the total column
Resultado
----------
NOME Total
g1 0
g2 3
Assuming the following work DDL:
CREATE DATABASE 'stackoverflow';
CREATE TABLE 'Grupo' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'Nome' varchar(85) DEFAULT NULL,
'bol_ativo' char(1) DEFAULT 'S',
PRIMARY KEY ('id')
);
CREATE TABLE 'Processo' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'numero' int(11) DEFAULT NULL,
'ativo' char(1) DEFAULT 'S',
'encaminhado' char(1) DEFAULT 'N',
PRIMARY KEY ('id')
);
And in response to the following query DML:
INSERT INTO 'Grupo' ('id','Nome','bol_ativo')
VALUES (1,'g1','S'),
(2,'g2','S'),
(3,'g3','S'),
(4,'g4','N');
INSERT INTO 'Processo' ('id','numero','ativo','encaminhado')
VALUES (1,1,'S','N'),
(2,1,'S','N'),
(3,1,'S','N'),
(4,2,'S','N'),
(5,2,'S','N'),
(6,1,'S','N'),
(7,2,'S','N'),
(8,2,'S','N'),
(9,4,'S','S'),
(10,2,'N','N'),
(11,3,'N','N');
SELECT 'g'.'Nome' AS 'NOME', COUNT('p'.'id') AS 'Total'
FROM 'Grupo' 'g'
LEFT JOIN (
SELECT * FROM 'Processo' 'p'
WHERE 1=1
AND 'p'.'ativo' = 'S'
AND 'p'.'encaminhado' = 'N'
) p ON ('p'.'numero' = 'g'.'id')
WHERE
'g'.'Bol_ativo' = 'S'
GROUP BY 'g'.'Nome';
[Paul Dubois at al, 2005], MySQL® 5.0 : Certification Study Guide
p>