Show query (Problem Id's different)

Question

Show query (Problem Id's different)

Navigation

#1 by (4 votes)
#2 by (2 votes)
#3 by (2 votes)

-4

I have a rather large query but then when I ask to show it comes with the mixed id's.

Is there anything wrong with the query?

 SELECT *  FROM tb_detalhe_trabalhador1 inner join tb_empresa on 
tb_detalhe_trabalhador1.id = tb_empresa.id 
Inner Join tb_detalhe_trabalhador2 on tb_detalhe_trabalhador2.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador3 on tb_detalhe_trabalhador3.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador4 on tb_detalhe_trabalhador4.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador5 on tb_detalhe_trabalhador5.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador6 on tb_detalhe_trabalhador6.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador7 on tb_detalhe_trabalhador7.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador8 on tb_detalhe_trabalhador8.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador9 on tb_detalhe_trabalhador9.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador10 on tb_detalhe_trabalhador10.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador11 on tb_detalhe_trabalhador11.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador12 on tb_detalhe_trabalhador12.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador13 on tb_detalhe_trabalhador13.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador14 on tb_detalhe_trabalhador14.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador15 on tb_detalhe_trabalhador15.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador16 on tb_detalhe_trabalhador16.id = tb_empresa.id
Inner Join tb_equipamentos on tb_equipamentos.id = tb_empresa.id
WHERE AlvaraValidade or AcidenteValidade or SeguroValidade or FinancasValidade or 
SocialValidade or RemuneracaoValidade or InstaladorValidade or MontadorValidade or
 MedicaValidade or ProjectistaValidade or GasValidade or RedesValidade or
 SoldadorValidade or MecanicoValidade or ClasSoldadorValidade or MaquinaValidade1 or
 MaquinaValidade2 or MaquinaValidade3 or MaquinaTopoValidade BETWEEN CURDATE() AND
 CURDATE() + INTERVAL 10 DAY";

  //Faço isto primeiro
  if (($row[11] != '0000-00-00' && estaParaExpirar($row[11], "10")) 
    or ($row[12] != '0000-00-00' && estaParaExpirar($row[12], "10"))
    or ($row[13] != '0000-00-00' && estaParaExpirar($row[13], "10")) 
    or ($row[14] != '0000-00-00' && estaParaExpirar($row[14], "10")) 
    or ($row[15] != '0000-00-00' && estaParaExpirar($row[15], "10")) 
    or ($row[16] != '0000-00-00' && estaParaExpirar($row[16], "10")) 
    or ($row[17] != '0000-00-00' && estaParaExpirar($row[17], "10"))) {     
 $Nome1 = '<p>Nome: '.$row[10].'</p>' ;}
 if ($row[11] != '' && ($row[11] != NULL && ($row[11] != '0000-00-00' ))) {
 $MedicaValidade = estaParaExpirar($row[11], "10") ? '<p>
 A data da Ficha de aptidao  Medica vai expirar no dia '.$row[11].'</p>' : '';}

 //Depois no corpo do email faço so isto
 // corpo da mensagem
  $PHPMailer->Body = "<body>
  <br>
 ".$Trabalhador."
 ".$Nome1."
 ".$MedicaValidade."

php query

asked by anonymous 13.08.2014 / 12:52

3 answers

PHP or Java? Positives and negatives [closed] Is it possible to register .go domains? [closed]

score 4 · Answer 1

I imagine your joins are wrong:

tb_detalhe_worker1.id = tb_company.id
tb_equipment.id = tb_company.id

tb_company

id company_name
1 SamSumg
2 HP
3 Dell

tb_equipment

id equipment
1 computer
2 laptop
3 scanner

tb_details_worker1

id name
1 Joao
2 Maria
3 Peter

You should be aware of the primary keys and relate them correctly. tb_detalhe_trabalhador.id_empresa = empresa.id
tb_equipment.id_company = company.id

Try to make the INNER JOINS and the WHERE in a few steps, preferably table by table to make sure the results are matching.

score 2 · Answer 2

According to the exclamation of Mr. @Motta, the correct thing is to make a table to record the details of each worker, so the same rule for the table where the equipment registers, and company (assuming that there you register the affiliates, matrix etc ).

After normalization, make a query conditioning tb_worker, tb_equipment, tb_company through their respective Pk and / or Fk keys and the last between filter to pick up between the desired period.

score 2 · Answer 3

First, normalize your database. Even if the company or department is only going to have 16 employees, it is a very bad practice to stick with 16 tables in your database, because if another programmer has to mess with your system or create another program that uses your bank, he certainty will be lost, and database performance itself will be compromised by too many tables.

NOTE: Normalization is the pre-organization of data and the separation and removal of duplicate data. Wikipedia and Microsoft (English)

Once you have normalized the database, re-create your query according to the new tables created in normalization. In addition to being much more readable, it increases the performance of your application.

And then, finally, use the ORDER BY clause to organize the query results.