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.
Just one correction: it’s not on the left side of the comparison, it’s on the left side of the
join
. Theleft join
considers the table on the left side offrom cursos c left join cursos p
, as long as theright join
considers the right-hand table independent of the comparison.– Ronaldo Araújo Alves
@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.
– João Martins
What do you mean "exchange" the tables of
JOIN
? That’s still a little confusing– Lone Tonberry
@Lonetonberry In the relationship of
cursos
withcursos
, what determines which table represents the course and which prerequisite is the linked fields. Emp.id_curso = c.fk_prereq
you determine that in thisselect
the alias tablec
is the main course while thep
is the prerequisite course, as if speaking to the bank: "I want the records ofcursos
(alias c), and I also want the name ofcursos
(alias p) whose Ids are equivalent to my "prerequisite id"cursos
(alias c)"– Ronaldo Araújo Alves
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
wherep.idcurso
relates to the table that we want to "add" to the main,cursos c
).– João Martins