Array
(
)

SQL - Agrupar irmãos de pais diferentes (Composição Familiar)

Ruy Guerra
   - 23 out 2014

Boa tarde, a todos os amigos do fórum.
Necessito de uma ajuda de todos vocês.
-Necessidade: Listar os alunos que são irmãos (consanguíneos ou não)
- O que foi realizado: Criei uma query que consegue pesquisar todos os irmãos e seus respectivos pais, seja os pais consanguíneos ou não.
- Problemática: Preciso agrupar os irmãos, porem ao colocar no parâmetro do GROUP BY para agrupar o CODMAE quando o pai não é consanguíneo de uns dos alunos que são irmãos, o que ocorre é que o grup by não agrupa essa família.

Código da Query
SELECT
EALUNOS.MATRICULA,
EALUNOS.NOME,
EMATRICPL.CODTUR,
EALUNOS.MAE,
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE) AS TOTALMAE,
EALUNOS.PAI,
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI) AS TOTALPAI,
((SELECT COUNT (EA.MATRICULA) FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE)
+
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI)) AS QDTOTAL
FROM
EALUNOS LEFT OUTER JOIN EMATRICPL ON EMATRICPL.MATALUNO = EALUNOS.MATRICULA
WHERE
EMATRICPL.PERLETIVO='2014' AND
EMATRICPL.SITMAT='01' AND
EMATRICPL.CODFILIAL = '7'
GROUP BY
EALUNOS.MAE, EALUNOS.PAI,
EALUNOS.MATRICULA,
EALUNOS.NOME,
EMATRICPL.CODTUR,
EMATRICPL.PERLETIVO,
EMATRICPL.SITMAT,
EMATRICPL.CODFILIAL

HAVING
((SELECT COUNT (EA.MATRICULA) FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE)
+
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI)) =3
Vejamos o resultado obtido
Clique na imagem para abrir em uma nova janela

Como eu posso fazer para agrupar os dois irmãos Arturo Garcia e Rodrigo Garcia, sendo que o pai deles é o mesmo porem a mãe não é.
Agradeço a todos pela ajuda.

MATRICULA NOME CODTUR MAE TM PAI TP QDTOTAL
---------------------------------------------------------------------------------------------------------------------------------------
3819 | Pedro Carvalho |2ª série A |12108 |2 |12107 |1 |3 |
5546 | Samuel Filho |8º ano A |12108 |2 |20289 |1 |3 |
6397 | Artur Santali |5º ano B |18948 |2 |18947 |1 |3 |
8047 | Leonardo Santali |1º ano C| |18948 |2 |23317 |1 |3 |
----> 6427 | Arturo Garcia |5º ano B |19039 |1 |19038 |2 |3 |
8002 | Victor Zangar |2º ano A |19084 |2 |19082 |1 |3 |
6442 | Mariana Zangar |5º ano A |19084 |2 |19083 |1 |3 |
3401 | Victor Basset |3ª série B |22833 |2 |5572 |1 |3 |
7857 | Antonio Sulzba |4º ano C |22833 |2 |22832 |1 |3 |
----> 8011 | Rodrigo Garcia |2º ano D |23219 |1 |19038 |2 |3 |

Emersonbrito
   - 23 out 2014

Boa Tarde Prezado!

Olha pelo que eu analisei do seu problema é bem simples:

Copiei o script abaixo que gera uma tabela com exemplo (tabela temporária no SQL), para você entender a adaptar a sua query.

--===============================================================

-- Apaga uma tabela temporária caso exista...
if exists (select * from tempdb..sysobjects (nolock) where [name] like '#tmp1_%')
begin drop table #tmp1 end

-- Cria uma Tabela de Teste para Exemplo...
SELECT TOP 0 identity(int,1,1) as id,
cast(null as varchar(100)) as 'Filho',
cast(null as varchar(100)) as 'Pai',
cast(null as varchar(100)) as 'Mae'
INTO #tmp1

-- Mostra Tabela Criada Vazia para Exemplo...
SELECT * from #tmp1

-- Inserindo Registros para Exemplo...
INSERT INTO #tmp1
SELECT 'Ana' as Filho, 'Jose' as PAI, 'Maria' as MAE
UNION
SELECT 'Beatriz' as Filho, 'Jose' as PAI, 'Maria' as MAE
UNION
SELECT 'Carlos' as Filho, 'Jose' as PAI, 'Claudia' as MAE
UNION
SELECT 'Daniel' as Filho, 'Jose' as PAI, 'Claudia' as MAE
UNION
SELECT 'Elias' as Filho, 'Raimundo' as PAI, 'Maria' as MAE
UNION
SELECT 'EMERSON' as Filho, 'ENIO' as PAI, 'EUNICE' as MAE

-- Registros Inseridos - Sendo que o id 6 (EMERSON) não é consanguineo...
SELECT * FROM #tmp1

-- Sua Problemática Resolvida!!!
SELECT t1.*
FROM #tmp1 t1
WHERE (PAI IN (SELECT Pai FROM #tmp1 group by Pai having count(*) > 1)
OR MAE IN (SELECT Mae FROM #tmp1 group by Mae having count(*) > 1))

--===============================================================

Espero ter lhe ajudado!

Um abraço!

Ruy Guerra
   - 23 out 2014

Olá Emerson!

Como vai?

Muito obrigado pelo retorno, agradeço pela ajuda.

bom... vamos lá, testei o que você descreveu e infelizmente não atendeu, deixa eu explicar detalhadamente.

- Eu listei todos os irmãos, porem eu tenho que agrupa-los, para agrupar utilizei o campo do código "MAE", então o group by entende que aquele campo é o campo chave para agrupamento (Figura 01), porem eu tenho um caso em que exite um pai "Cod: 19038" que ele tem dois filhos porem com mães diferentes "cods: 19039 e 23219", então eu gostaria de agrupar esse pai (Figura 02).

Como faço isso?

(Figura 01) Agrupado por Código da Mãe.

Clique na imagem para abrir em uma nova janela

(Figura 02) Resultado de como deve ficar - agrupo todas as mães identificas que possuem o mesmo pais e que for diferente desse universo é agrupado pelo código do pai.

Clique na imagem para abrir em uma nova janela

Aguardo o seu retorno, muito obrigado.

Emersonbrito
   - 24 out 2014

...Bom eu entendi que vc. queria listar os Irmãos consanguineos.

Agora Há três situações:

1º) Agrupamento por PAI iguais: (SELECT Pai FROM #tmp1 group by Pai having count(*) > 1)
2º) Agrupamento por MAE iguais: (SELECT Mae FROM #tmp1 group by Mae having count(*) > 1)
3º) Agrupamento por PAI e MAE diferentes: (SELECT Pai FROM #tmp1 group by Pai having count(*) = 1) OU (SELECT Mae FROM #tmp1 group by Mae having count(*) = 1)

Fazendo o SELECT separado de cada um destes juntá-los usando UNION.

Agora no primeiro e segundo caos que vc. cita, vc. deseja agrupar para "somar ou contar" os valores da coluna "TOTALPAI" ou "TOTALMAE"? Que no caso deveria ser 2 ao invés de 4 seria isso?

Ruy Guerra
   - 24 out 2014

Boa tarde, Emerson!

Vou aplicar a logica, os campos TOTALPAI E TOTALMAE, indica quantos alunos estão atrelados em seu código "MAE e PAI"

Ruy Guerra
   - 24 out 2014

Emerson coloquei um parâmetro Where filtrando os valores dos campos TOTALMAE e TOTALPAI, deu certo.

(SELECT MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL
FROM ALUNOS
WHERE TOTALPAI = 2 GROUP BY PAI, MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL HAVING COUNT(*) = 1)
UNION

(SELECT MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL
FROM ALUNOS
WHERE TOTALPAI = 1 GROUP BY MAE, MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL HAVING COUNT(*) = 1)

Clique na imagem para abrir em uma nova janela