mysql困难查询联合

Question

I have a table:
id  name
1   home
2   menu
3   contact

second table:
id  name   menu_id
1   menu1  2
2   menu2  2
3   menu3  2

and I need this output:
1   home
2   menu
  1   menu1 2
  2   menu2 2
  3   menu3 2
3   contact

我用工会尝试过这个，但我失败了。有人知道在这里使用什么查询吗？谢谢!

Answer 1

select t1.id, t2.id, t1.name, t2.name
  from t1
  left join t2 on t2.menu_id = t1.id
 order by t1.id, t2.id;

好的，如果您想要一个与您发布的数据集完全相同的数据集，请执行以下查询:

select case
         when t1Id < 0 then null
         else t1Id
       end as t1Id, nullif(t2Id, 0) as t2Id, t1Name, t2Name
  from (
     select id as t1Id, 0 as t2Id, name as t1Name, null as t2Name
       from t1
      union
     select -menu_id as t1Id, id as t2Id, null as t1Name, name as t2Name
       from t2
    ) allTables
  order by abs(allTables.t1Id), abs(allTables.t1Id) * 100 + allTables.t2Id;