select multiple tables in mysql

Question

select multiple tables in mysql

Navigation

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

0

Good morning, I need to make the following query:

I have 4 tables, the first is the main one, in which with id is foreign in the other 3 tables. I need to get the date and description of each of the tables where the id_tabla1 is present, in some it may have more records than in the others, that's my problem

Is it possible to relate these tables?

Table 1 main

table_id1
Name

Table 2

id_table2
Date
Description
fk_table1

Table 3

table_id3
Date
Description
fk_table1

Table 4

id_table4
Date
Description
fk_table1

I want to get something more or less like this:

mysql

asked by FeRcHo 30.12.2016 в 16:51

source

4 answers

Pass and get custom class parameters to Intent Problem in consulting BD with related tables

score 2 · Answer 1

First you need to obtain a table with all possible combinations of id and date that may occur and then use that result to extract the data from table2, table3 and table 4, like this:

SELECT tabla1_fecha.idtabla1, tabla2.fecha, tabla2.descripcion, tabla3.fecha, tabla3.descripcion, tabla4.fecha, tabla4.descripcion 
FROM 
(
    SELECT tabla1.idtabla1, fechas.fecha
    FROM tabla1 
    INNER JOIN 
    (
        SELECT fecha FROM tabla2
        UNION
        SELECT fecha FROM tabla3
        UNION
        SELECT fecha FROM tabla4    
    ) fechas
) tabla1_fecha 
LEFT JOIN tabla2 ON tabla1_fecha.idtabla1 = tabla2.fk_tabla1 AND tabla1_fecha.fecha = tabla2.fecha
LEFT JOIN tabla3 ON tabla1_fecha.idtabla1 = tabla3.fk_tabla1 AND tabla1_fecha.fecha = tabla3.fecha
LEFT JOIN tabla4 ON tabla1_fecha.idtabla1 = tabla4.fk_tabla1 AND tabla1_fecha.fecha = tabla4.fecha;

In MySQL I get the following result:

idtabla1    fecha         descripcion   fecha           descripcion fecha       descripcion
1           2016-12-29    prueba          2016-12-29    prueba      2016-12-29  prueba
1           2016-12-30    prueba2

score 2 · Answer 2

I need to get the date and description of each of the tables where the table_id1 is present, in some it may have more records than in the others, that's my problem

You could use UNION , so for example:

SELECT * 
FROM (
  SELECT 'tabla2' as tabla, fk_tabla1, fecha, descripcion FROM tabla2
  UNION SELECT 'tabla3', fk_tabla1, fecha, descripcion FROM tabla3
  UNION SELECT 'tabla4', fk_tabla1, fecha, descripcion FROM tabla4
) A
WHERE fk_tabla1 = 1

The result would be the following:

tabla    fk_tabla1   fecha        descripcion 
tabla2   1           2016-12-29   prueba
tabla2   1           2016-12-30   prueba2
tabla3   1           2016-12-29   prueba
tabla4   1           2016-12-29   prueba

score 1 · Answer 3

If you can relate your tables, once created you can use this to get what you are looking for:

SELECT t1.id_tabla1, t2.fecha,t2.descripcion, t3.fecha,t3.descripcion,t4.fecha,t4.descripcion
FROM tabla1 t1 
INNER JOIN tabla2 t2 ON t2.fk_tabla1 = t1.id_tabla1
INNER JOIN tabla3 t3 ON t3.fk_tabla1 = t1.id_tabla1
INNER JOIN tabla4 t4 ON t4.fk_tabla1 = t1.id_tabla1

score 1 · Answer 4

With left outer join using table1 as the principal and obtaining the values from table2, table3 and table4 if the record exists that matches the id in table1


SELECT t1.id_tabla1, t2.fecha, t2.descripcion, 
                 t3.fecha, t3.descripcion,
                 t4.fecha, t4.descripcion 
   from 
      (  SELECT tabla1.id_tabla1, fechas.fecha
         FROM tabla1 
         INNER JOIN (
            SELECT fecha FROM tabla2 UNION
            SELECT fecha FROM tabla3 UNION
            SELECT fecha FROM tabla4 ) fechas
       ) t1 
    left join tabla2 as t2
         on t1.id_tabla1 = t2.fk_tabla1 and t1.fecha = t2.fecha
    left join tabla3 as t3
         on t1.id_tabla1 = t3.fk_tabla1 and t1.fecha = t3.fecha
    left join tabla4 as t4
         on t1.id_tabla1 = t4.fk_tabla1 and t1.fecha = t4.fecha
   wHERE t1.id_tabla1 = 1