Search in mysql of 4 tables

Question

Search in mysql of 4 tables

Navigation

#1 by (0 votes)

1

Good friends I hope you can help me, the problem is the following .. I have 4 tables in MYSQL , I want to search in each of them

Table field1:

id,numero

Table field2:

nombre, apellido

Table field 3:

id,condicion,identificador

Table field 4:

id,razonsocial

My intention is to show the first, last name of the table2 , razonsocial of the tabla4 , and id, number of the tabla1 this is my query

  <?php
     $sql="SELECT DISTINCT 
      tabla1.id, 
      tabla1.numero, 
      tabla2.nombre, 
      tabla2.apellido, 
      tabla4.razonsocial 
      FROM tabla1, tabla2, tabla3, tabla4 
      WHERE 
      tabla1.numero=tabla3.id 
      AND (tabla3.condicion=1 AND tabla3.identificador=tabla4.id) 
      OR (tabla3.condicion=2 AND tabla3.identificador=tabla2.id)";

      $query = mysqli_query($con, $sql);

       while ($row=mysqli_fetch_array($query)){
          $id=$row["id"];
            $numero=$row["numero"]; 
            $nombre=$row['nombre'];
            $apellido=$row['apellido'];
            $razonsocial=$row['razonsocial'];
       }
      echo $nombre." ".$apellido;
  ?>

I hope you can help me, thank you very much !!

I leave you a clearer EXAMPLE:

TABLE1 = invoice, TABLE2 = Person, TABLE3 = Client, TABLE4 = Company

Suppose I need to look up the name of the Customer BELONGING TO SUCH INVOICE knowing that that customer can be a PERSON OR A COMPANY

In this case I have the identifier ... if identifier = 1 I should look in the table of COMPANIES (table4) otherwise if identifier = 2 I should look in the PEOPLE table (table2)

mysql php

asked by David 10.09.2017 в 18:23

source

1 answer

How to create a remote connection from my Mysql Workbench to Codeanywhere How to reload a div when sending a form?

score 0 · Answer 1

You have a problem to join the tables with the foreing keys, therefore it is advisable that you add to the pivot table that is Table 3 so I can see, add a data_id that would be the foreing key to Table 2.

Tabla 1         Tabla 3                 
id  numero      id  condicion   identificador   datos_id        
1   65           1    1           1              2              
2   45           2    2           2              1                              

        Tabla 2                 Tabla 4 
        id  nombre  apellido    id  razonsocial
        1   a        g           1  casado
        2   b        h           2  soltero

The query that I think may work is:

SELECT tabla2.nombre , tabla2.apellido , tabla4.razonsocial , tabla1.id , tabla1.numero
FROM tabla1 , tabla2 , tabla3 , tabla4
WHERE (tabla1.id = tabla3.identificador) AND (tabla3.condicion = tabla4.id) 
  AND (tabla3.datos_id = tabla2.id)