INNER JOIN does not return database result

Question

I’m trying to use this code:

SELECT *
FROM tb_trabalhador
INNER JOIN tb_detalhe_trabalhador ON Tb_trabalhador.id = tb_detalhe_trabalhador.tb_trabalhador_id

And it returns the blank result.

Is there any way around this with another code?

tb_details_worker:

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tb_trabalhador_id` int(11) DEFAULT NULL,
  `tb_funcoes_id` int(11) DEFAULT NULL,
  `MedicaValidade` date DEFAULT NULL,
  `MedicaAnexo` longblob,
   (...)

tb_worker:

`id` int(11) NOT NULL AUTO_INCREMENT,
`Nome` varchar(200) DEFAULT NULL,
`Morada` text,
`Tipo` varchar(45) DEFAULT NULL,
(...)

Answer 1

Your Join syntax is apparently correct.

What remains you check, is whether when entering the details of the worker, if the value of the tb_trabalhador.id desired is being included in the tb_detalhe_trabalhador.tb_trabalhador_id, for the relationship to be complete, since the condition is precisely this:

tb_trabalhador.id = tb_detalhe_trabalhador.tb_trabalhador_id

To get the correct ID, if you are entering a new worker and their details in the same series of operations, one possibility is to use the function LAST_INSERT_ID( ) right after entering the worker, and then insert the details with the ID returned by the function.

Taking advantage, see this issue about joins, and note the Ids of the example tables and the results if the condition of the ONwhether or not.

Answer 2

Updating

After discussion in the comments of this reply, it is clear that your query is not returning results as by inserting the records into the database you are not applying to the field tb_trabalhador_id of your table tb_detalhe_trabalhador the value entered in the field id of your table tb_trabalhador.

With the exception of the typing error in the table name I mentioned in the "original answer", your query is well constructed and does not return results unless there are no results related by the fields indicated in it.

---

Original Response

Your query is in condition and well formatted, but I think you have a typo in the table name:

Tb_trabalhador

It should be with t minuscule:

tb_trabalhador

^{Or vice versa.}

Consultation:

SELECT *
FROM tb_trabalhador
INNER JOIN tb_detalhe_trabalhador ON tb_trabalhador.id = tb_detalhe_trabalhador.tb_trabalhador_id

---

9.2.2 Identifier Case Sensitivity

In Mysql, Databases correspond to Directories Within the data directory. Each table Within a database Corresponds to at least one file Within the database directory (and possibly more, Depending on the Storage engine). Consequently, the case sensitivity of the underlying Operating system plays a part in the case sensitivity of database and table Names. This Means database and table Names are not case sensitive in Windows, and case sensitive in Most Varieties of Unix. One notable Exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also Supports UFS volumes, which are case sensitive just as on any Unix.

That translated:

In Mysql, the databases correspond to the directories within the data directory. Each table within a database corresponds to at least one file in the database directory (and possibly more, depending on the storage engine). Consequently, the sensitivity of the underlying operating system to upper or lower case letters plays a role in the sensitivity of upper or lower case letters in database and table names. This means that database and table names are not case-sensitive in Windows, but are in most varieties of Unix. A notable exception is Mac OS X, which is based on Unix, but uses a standard file system type (HFS+), which does not differentiate case from case. However, Mac OS X also supports UFS volumes, which are sensitive, just like on any Unix.