Question with SELECT instruction from a self-relative table

Asked

Viewed 96 times

3

Hello dev’s I own the following table courses(self relational):

desc cursos;

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id_curso  | int(11)     | NO   | PRI | NULL    | auto_increment |
| nome      | varchar(30) | YES  |     | NULL    |                |
| horas     | int(11)     | YES  |     | NULL    |                |
| valor     | float(10,2) | YES  |     | NULL    |                |
| fk_prereq | int(11)     | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

Where fk_prereq is the foreign key that references the course id (from the table itself) that is prerequisite for the other, so if that column assumes a value NULL is because such a course has no prerequisite.

I have some data registered in this table:

select * from cursos;

+----------+-----------------------+-------+--------+-----------+
| id_curso | nome                  | horas | valor  | fk_prereq |
+----------+-----------------------+-------+--------+-----------+
|        1 | db relacional         |    20 | 400.00 |      NULL |
|        2 | business intelligence |    40 | 800.00 |         1 |
|        3 | relatorios avançados  |    20 | 600.00 |         2 |
|        4 | logica programacao    |    10 | 200.00 |      NULL |
|        5 | ruby                  |    40 | 400.00 |         4 |
+----------+-----------------------+-------+--------+-----------+

My question is this, if I want to bring the name, hours, value and the prerequisite of this course, I would usually try to do something like:

select      c.nome
        ,   c.horas
        ,   c.valor
        ,   ifnull(p.nome, '---') as 'pre-requisito' 
from        cursos c 
left join   cursos p on c.id_curso = p.fk_prereq

But that way the projection goes wrong and the result I have is the following:

+-----------------------+-------+--------+-----------------------+
| nome                  | horas | valor  | pre-requisito         |
+-----------------------+-------+--------+-----------------------+
| db relacional         |    20 | 400.00 | business intelligence |
| business intelligence |    40 | 800.00 | relatorios avançados  |
| logica programacao    |    10 | 200.00 | ruby                  |
| relatorios avançados  |    20 | 600.00 | ---                   |
| ruby                  |    40 | 400.00 | ---                   |
+-----------------------+-------+--------+-----------------------+

The correct way would be to do with this instruction:

select      c.nome
        ,   c.horas
        ,   c.valor
        ,   ifnull(p.nome, '---') as 'pre-requisito' 
from        cursos c 
left join   cursos p on p.id_curso = c.fk_prereq

And the result would be:

+-----------------------+-------+--------+-----------------------+
| nome                  | horas | valor  | pre-requisito         |
+-----------------------+-------+--------+-----------------------+
| db relacional         |    20 | 400.00 | ---                   |
| business intelligence |    40 | 800.00 | db relacional         |
| relatorios avançados  |    20 | 600.00 | business intelligence |
| logica programacao    |    10 | 200.00 | ---                   |
| ruby                  |    40 | 400.00 | logica programacao    |
+-----------------------+-------+--------+-----------------------+

In short I did not understand why a simple exchange of on c.id_curso = p.fk_prereq for on p.id_curso = c.fk_prereq, would totally change the expected result, because in a table that is not self-relative if I do the first way I tried the result already comes out correct.

1 answer

3


If you read the article Visual Representation of SQL Joins in the Codeproject will be able to quickly understand why the behavior.

In your case it would be as follows:

inserir a descrição da imagem aqui

Basically, a LEFT JOIN allows to obtain all the results of the table A regardless of whether there is an association in the table B, and at the same time will bring the table records B associated by the key fk_prereq at the table A (returning NULL otherwise).


In your case I believe the results are different because on the left side of the JOIN, c.id_curso = p.fk_prereq, was the table column A, which is leading the compiler to the idea of "switching" the tables of the JOIN precisely because it is the same table on both sides, that is why when we change the columns it works correctly.

  • 3

    Just one correction: it’s not on the left side of the comparison, it’s on the left side of the join. The left join considers the table on the left side of from cursos c left join cursos p, as long as the right join considers the right-hand table independent of the comparison.

  • 1

    @Ronaldoaraújoalves was what I wanted to say, but yes, perhaps it is more correct to say it this way, even to avoid confusion. Thank you, edited reply.

  • What do you mean "exchange" the tables of JOIN ? That’s still a little confusing

  • 2

    @Lonetonberry In the relationship of cursos with cursos, what determines which table represents the course and which prerequisite is the linked fields. Em p.id_curso = c.fk_prereq you determine that in this select the alias table c is the main course while the p is the prerequisite course, as if speaking to the bank: "I want the records of cursos(alias c), and I also want the name of cursos(alias p) whose Ids are equivalent to my "prerequisite id" cursos(alias c)"

  • 1

    Basically, one uses a LEFT JOIN then the table that should be on the left side is the one that you want to call (ex.: left join cursos p on p.id_curso = c.fk_prereq where p.idcurso relates to the table that we want to "add" to the main, cursos c).

Browser other questions tagged

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