Repeat LEFT JOIN with other parameters in the same query

Navigation
1

I have 2 tables:

FATHER: 

id
nome

Child: 

id
idpai
sexo_filho

I wanted to do a SELECT that sums up the amount of children man and women, that's the problem, I can only do with one of the sex: 

SELECT pai.nome, count(distinct filho.id) as somahomem 
FROM pai 
LEFT JOIN filho ON pai.id = filho.idpai AND conta_filho.sexo = 'm'

How to do with both sexes? Have the value of the two?

    
asked by anonymous 02.10.2015 / 21:19

3 answers

4

You can use the CASE clause to count only one condition. See the example below: 

 SELECT
          PAI.NOME,
          COUNT(DISTINCT(CASE WHEN FILHO.SEXO = 'F' THEN FILHO.ID ELSE NULL END)) AS 'FEMININO',
          COUNT(DISTINCT(CASE WHEN FILHO.SEXO = 'M' THEN FILHO.ID ELSE NULL END)) AS 'MASCULINO'
 FROM
          PAI
          LEFT JOIN FILHO ON FILHO.ID_PAI = PAI.ID
 GROUP BY
          PAI.NOME

See working in SQLFiddle

    
02.10.2015 / 21:33
0

You can use more than JOIN : 

SELECT pai.nome, 
    count(distinct filho.id) as somahomem,
    count(distinct filho2.id) as somamulher
FROM pai 
    LEFT JOIN filho ON pai.id = filho.idpai AND filho.sexo = 'm'
    LEFT JOIN filho2 ON pai.id = filho2.idpai AND filho2.sexo = 'f'
GROUP BY 
    pai.nome;
    
02.10.2015 / 21:34
0

You can use SUM() with IF() 

SELECT 
  pai.nome, 
  SUM(if(filho.sexo = 'm', 1, 0)) as somahomem,
  SUM(if(filho.sexo = 'f', 1, 0)) as somamulher
FROM 
  pai 
  LEFT JOIN filho ON (pai.id = filho.idpai)
GROUP BY pai.id
    
02.10.2015 / 21:36
C # Regular string expression and Guid validation Convert to the nomenclature of R $ [duplicate]