Repeat LEFT JOIN with other parameters in the same query

Asked

Viewed 565 times

1

I have 2 tables:

FATHER:

id
nome

SON:

id
idpai
sexo_filho

I wanted to make a SELECT that adds up the amount of children male and female, 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 both?

mysql sql select join

3 answers

4


You can use the clause CASE to account for 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 on Sqlfiddle

  • Thanks, it’s going straight, there’s no way to put a distinct in it?

  • In that structure you informed, in my view, it is unnecessary the distinct as the result would be the same.

  • So my query is more complex, it has several Count and sum with other tables. Without the distict I don’t know why it will multiply the Counts between them....

  • I updated with DISTINCT.

0

You can use more than one 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;

0

You can use the 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

Browser other questions tagged mysql sql select join

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