I need to make the following query:
I have 4 tables, the first is the main, in which with the 'id' is foreign in the other 3 tables. I need to get the date and description of each of the tables where it presents the id_tabla1. In some tables I have more records than in the other.
Is it possible to relate these tables?
Table 1 main
I want to get something like this:
This type of operation is a bit of a pain in MySQL. In fact, the result is not particularly "relational", because each column is a separate list. You can't do a
join because there is no
You can generate one in MySQL using variables and then use aggregation. Here is an example with two tables:
select id_table1, max(t2_date) as t2_date, max(t2_desc) as t2_desc, max(t3_date) as t3_date, max(t3_desc) as t3_desc from ((select id_table1, NULL as t2_date, NULL as t2_desc, NULL as t3_date, NULL as t3_desc, 1 as rn from table1 t1 ) t1 union all (select fk_table1, date as t2_date, description as t2_desc, NULL as t3_date, NULL as t3_desc, (@rn1 := if(@fk1 = fk_table1, @rn1 + 1, if(@fk1 := fk_table1, 1, 1) ) ) as rn from table1 t1 cross join (select @rn1 := 0, @fk1 := 0) params order by fk_table1, date ) t1 union all (select fk_table1, NULL, NULL, date as t3_date, description as t3_desc (@rn2 := if(@fk2 = fk_table1, @rn2 + 1, if(@fk2 := fk_table1, 1, 1) ) ) as rn from table1 t1 cross join (select @rn2 := 0, @fk2 := 0) params order by fk_table1, date ) ) t group by id_table1, rn;