Identifying SQL Brothers

My idea is similar to the @Sorack idea , but I'm trying to do it without parenting. In this case, I set a IRMANDADE (borrowed from the AlexandreCavaloti proposal ) and then together with the student, returning an arbitrary code of brotherhood.

  

I have not tried to deal with the generalized case, where the brotherhood is defined by a generalized graph, and clicks are always brothers who share both parents. The solution proposed here offers the correct response for sibling clicks, other graph formats can have weird answers.

Determining the click code

Since all siblings are connected, using one of the siblings' codes as a code ensures that the codes between one sibling grouping will never conflict with the code of another sibling grouping. To always get the same code in the click, sets it to be the smallest student code pertaining to the click.

So we have the following brotherhoods:

COD_ALUNO | COD_IRMANDADE
1         | 1
2         | 1
3         | 1
4         | 4
5         | 4
7         | 7
8         | 7
11        | 11
12        | 11

Building a click

As we are assuming that the graph is a set of disconnected clicks, to detect each of these clicks is to only do a join of the ALUNOS_IRMAOS table with itself (more about auto join #

SELECT
    AI1.COD_ALUNO,
    CASE
        WHEN AI1.COD_ALUNO < min(AI2.COD_ALUNO_IRMAO)
        THEN AI1.COD_ALUNO
        ELSE min(AI2.COD_ALUNO_IRMAO)
    END AS COD_IRMANDADE
FROM
    ALUNOS_IRMAOS AI1
        LEFT JOIN ALUNOS_IRMAOS AI2
            ON AI1.COD_ALUNO = AI2.COD_ALUNO
GROUP BY AI1.COD_ALUNO

Note that case and min ensure COD_IRMANDADE is always the smallest possible click.

Whole query

I can take the brotherhood clique query and use it in the final query as a CTE or as a subquery. I think it's more like CTE:

WITH IRMANDADE AS (
  SELECT
      AI1.COD_ALUNO,
      CASE
          WHEN AI1.COD_ALUNO < min(AI2.COD_ALUNO_IRMAO)
          THEN AI1.COD_ALUNO
          ELSE min(AI2.COD_ALUNO_IRMAO)
      END AS COD_IRMANDADE
  FROM
      ALUNOS_IRMAOS AI1
          LEFT JOIN ALUNOS_IRMAOS AI2
              ON AI1.COD_ALUNO = AI2.COD_ALUNO
  GROUP BY AI1.COD_ALUNO
)
SELECT
    A.COD_ALUNO, A.NM_ALUNO, A.NM_PAI, A.NM_MAE, I.COD_IRMANDADE
FROM ALUNOS A 
    LEFT JOIN IRMANDADE I
        ON A.COD_ALUNO = I.COD_ALUNO

Result:

COD_ALUNO | NM_ALUNO | NM_PAI  | NM_MAE   | COD_IRMANDADE
1         | GABRIEL  | SERGIO  | CELIA    | 1
2         | VITOR    | SERGIO  | CELIA    | 1
3         | GEOVANNE | SERGIO  | CELIA    | 1
4         | BRUNO    | WAGNER  | PAULA    | 4
5         | PEDRO    | WAGNER  | PAULA    | 4
6         | LARISSA  | TIAGO   | LAURA    | (null)
7         | GRAÇA    | PEDRO   | ISADORA  | 7
8         | MELISSA  | PEDRO   | ISADORA  | 7
9         | ENZO     | RAFAEL  | CAROLINE | (null)
10        | RAFAEL   | RAFAEL  | CELIA    | (null)
11        | MARIANE  | DANIEL  | MAITE    | 11
12        | TATIANE  | DANIEL  | MAITE    | 11
13        | MARIA    | RODOLFO | DANIELA  | (null)

Notice that even the% s of% s that you had predicted in the desired response occur here. To get consecutive brotherhood indexes, you would need to use null on CTE ROW_NUMBER .

See running SQLFiddle

Perhaps the easiest way is to group by parent name combination (neglecting combinations that are both empty). This assumes that the names are unique:

WITH PARENTALIDADE AS (
  SELECT ROW_NUMBER() OVER(ORDER BY A.NM_PAI, A.NM_MAE) AS COD_UNICO_DOS_IRMAOS,
         ISNULL(A.NM_PAI, '') AS NM_PAI,
         ISNULL(A.NM_MAE, '') AS NM_MAE,
         COUNT(1) AS OCORRENCIAS
    FROM ALUNOS A
   WHERE A.NM_PAI IS NOT NULL
      OR A.NM_MAE IS NOT NULL
   GROUP BY NM_PAI,
            NM_MAE
  HAVING COUNT(1) > 1
)
SELECT A.*,
       P.COD_UNICO_DOS_IRMAOS
  FROM ALUNOS A
       LEFT JOIN PARENTALIDADE P ON P.NM_PAI = ISNULL(A.NM_PAI, '')
                                AND P.NM_MAE = ISNULL(A.NM_MAE, '')

The structure of the table seems to me flawed, but with the current one and considering that these flaws can cause inconsistency this solution seems to be feasible.

You can check here the fiddle of the working example

Gabriel, in this case you have a many-to-many relationship, wherever this is represented in the logical model you need another table to represent in a standard way in the relational model. For example: In this case you could create a new table called "Family" or "Brotherhood", there would be an ID for each brotherhood and the relation of each student (foreign keys).

CD_IRMANDADE CD_ALUNO
1            1
1            2
1            3
2            4
2            5

Here is an example link on how to handle many-to-many relationships. Types of relationship