当前位置: 动力学知识库 > 问答 > 编程问答 >

Select multiple tables in mysql

问题描述:

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

  1. id_table1
  2. Name

Table 2

  1. id_table2
  2. date
  3. description
  4. fk_table1

Table 3

  1. id_table3
  2. date
  3. description
  4. fk_table1

Table 4

  1. id_table4
  2. date
  3. description
  4. fk_table1

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 join key.

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;
分享给朋友:
您可能感兴趣的文章:
随机阅读: