Correct way to make a large inner join

Navigation
2

Good day, people. I'm a beginner in php and I'm developing a program for file box control. I have a database in which the "box" table is the main one, it has several references to other tables. Here's how: 

<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 such as 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 is looking for infinitely. When I limited the query to 20 results I noticed that it is picking up some repeated values and not picking up others.

    
asked by anonymous 10.06.2016 / 15:40

2 answers

2

I believe the connection condition ON is missing for 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 KEY for the Profile table.

    
10.06.2016 / 15:50
2

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

For example:

  

table1: 50 records

     

table2: 5 records

With a SELECT statement like this: 

SELECT *
  FROM tabela1
 INNER JOIN tabela2

You would have a return of 250 records , because for each row in table1 SELECT returns 5 rows , one for each row of table2 .

You indicate the link between the tables by using FK - Foreign Key ) .

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

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

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

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

I hope I have helped.

    
10.06.2016 / 15:49
Limit inputs dynamically Error removing accents