Identifying SQL brothers

Question

I built this link as an example of where I want to get http://sqlfiddle.com/#! 9/72f4b5/4

I have the student table. I have the table with the sibling ids

My problem is, I need to make a select so that I can make a name appear only at a time and produce an ID to link the siblings:
With this query

SELECT  * FROM ALUNOS A 
INNER JOIN ALUNOS_IRMAOS AI
ON A.COD_ALUNO = AI.COD_ALUNO
WHERE A.COD_ALUNO = AI.COD_ALUNO OR A.COD_ALUNO = AI.COD_ALUNO
GROUP BY A.COD_ALUNO, A.NM_ALUNO, A.NM_PAI, A.NM_MAE

I managed to arrive at the following result:

I need a query to help me get the image result 3.

Answer 1

My idea is similar to idea by @Sorack, but I am trying to do without joining by the name of the parents. In this case, I define a IRMANDADE (name borrowed from proposal by @Alexandrecavaloti) and then together with the student, returning a brotherhood code arbitrary.

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

Determining the click code

Since all siblings are linked, using one of the siblings' codes as click code ensures that the codes between a sibling grouping will never conflict with the code of another sibling grouping. To always pick up the same code at the click, sets it to be the smallest student code belonging to the click.

Thus, 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

Since we are assuming that the graph is a set of disconnected clicks, detecting each of these clicks is just making a table join ALUNOS_IRMAOS with itself (more about auto joining here and here).

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 the case and the min ensure that the COD_IRMANDADE always be as small as possible for the click.

Entire consultation

I can take the brotherhood click query and use it in the final query as a CTE or as a sub-query. I find it more elective as 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

Upshot:

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)

Realize that even the nulls that you had predicted in the desired response occur here. To catch consecutive sorority rates, you would need to use a ROW_NUMBER on the TEC IRMANDADE.

Behold running on Sqlfiddle

Answer 2

Perhaps the easiest way is to group by the combination of the parents' name (disregarding the combinations that are both empty). This considers the names to be 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 and considering that these failures may cause inconsistency this solution seems to be feasible.

You can check out here the fiddle of the working example

Answer 3

Gabriel, in this case you have a relationship of many to many, whenever this is represented in the logical model another table is necessary to represent in a normalized way in the relational model. For example: In this case you could create a new table called "Familia" or "Irmandade", there would be an ID for each fraternity and the relation of each student (foreign keys).

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

Follow a link with an example of how to treat many to many relationships. Types of relationship