Correct way to make a great Inner Join

Question

Good morning, you guys. I’m a beginner in php and I’m developing a program for controlling file boxes. I have a database in which the table "box" is the main one, it has several references to other tables. Next:

<br>CREATE TABLE `caixa` (<br>
`nro_caixa` int(6) NOT NULL,<br>
`auditor` varchar(150) NOT NULL,<br>
`id_cliente` int(5) NOT NULL,<br>
`observacao` text NOT NULL,<br>
`data_ini` date DEFAULT NULL,<br>
`data_fin` date DEFAULT NULL,<br>
`tipo_caixa` int(2) DEFAULT NULL,<br>
`data_cadastro` datetime NOT NULL,<br>
`data_descarte` datetime DEFAULT NULL,<br>
`obs_descarte` varchar(150) NOT NULL,<br>
`res_descarte` int(3) DEFAULT NULL,<br>
`id_departamento` int(3) DEFAULT NULL,<br>
`id_unidade` int(3) DEFAULT NULL,<br>
`id_tipo_doc` int(10) NOT NULL,<br>
`id_status` int(11) NOT NULL,<br>
`id_prateleira` int(11) DEFAULT NULL,<br>
`id_caixa` int(11) NOT NULL AUTO_INCREMENT,<br>
PRIMARY KEY (`id_caixa`),<br>
KEY `id_status` (`id_status`),<br>
KEY `id_prateleira` (`id_prateleira`),<br>
KEY `res_descarte` (`res_descarte`),<br>
KEY `id_unidade` (`id_unidade`),<br>
KEY `id_tipo_doc` (`id_tipo_doc`),<br>
KEY `id_departamento` (`id_departamento`),<br>
KEY `id_cliente` (`id_cliente`)<br>
)
ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=37259 ;

I need a way to get information from other tables like document type names, unit name, profile name, shelf name and department name that are fields in other tables.
What I’ve tried so far:

SELECT <br>a.id_caixa,<br> a.nro_caixa,<br> a.auditor,<br> a.observacao,<br> a.data_ini,<br> a.data_fin,<br> a.tipo_caixa,<br> b.nome,<br> c.nome,<br> d.nome,<br> e.nome,<br> f.nome<br> 
FROM CAIXA AS a<br>
INNER JOIN clientes AS b<br>
INNER JOIN departamento AS c<br>
INNER JOIN tipo_doc AS d<br>
INNER JOIN unidade as e<br>
INNER JOIN perfil_doc as f<br>
WHERE a.nro_caixa = $numero

What happens is that the bank looks endlessly. When I limited the query to 20 results I realized that it is picking up some repeated values and is not picking up others.

Answer 1

Buddy, what happens is that if you just add the INNER JOIN without indicating the links between the tables you will have as a result of the query an amount of records equal to multiplication of the lines of all the tables involved.

For example:

Table 1: 50 records

table 2: 5 records

With an instruction SELECT thus:

SELECT *
  FROM tabela1
 INNER JOIN tabela2

You’d get a return from 250 records, for each row of Tabela1 to SELECT returns 5 lines, one for each row of table2.

You indicate the link between tables through the use of foreign keys (FK - Foreign Key).

As foreign keys of your table are the fields that make references to other tables, are usually the primary keys of other tables.

Continuing the example, the correct then would be the following SELECT:

SELECT *
  FROM tabela1
 INNER JOIN tabela2 ON tabela1.chave_estrangeira = tabela2.chave_primaria

The use of ON in the INNER JOIN is that indicates the link between the tables and limits the result of the SELECT.

I hope I’ve helped.

Answer 2

I believe the condition of the call is missing ON for the INNER JOIN

SELECT
    a.id_caixa,
    a.nro_caixa,
    a.auditor,
    a.observacao,
    a.data_ini,
    a.data_fin,
    a.tipo_caixa,
    b.nome,
    c.nome,
    d.nome,
    e.nome
FROM
    CAIXA AS a
    INNER JOIN clientes AS b        ON b.id_cliente = a.id_cliente
    INNER JOIN departamento AS c    ON c.id_departamento  = a.id_departamento 
    INNER JOIN tipo_doc AS d        ON d.id_tipo_doc = a.id_tipo_doc
    INNER JOIN unidade AS e         ON e.id_unidade = a.id_unidade
WHERE
    a.nro_caixa = $numero

And also seems to be missing the KEY for the table Profile.