I now have multiple tables in the database
probably as shown in figure
schema
http://sqlfiddle.com/-sharp!9/1e31.
an and c tables are associated through b table, remote one-to-many
an and e tables are associated through d table, and remote one-to-many
b and d can be null
I want to get that row of data in an and the data of c and e associated with it through an Id of a.
at present, the closest way is
select a.*,
group_concat(lfc.id)
group_concat(lfc.name)
group_concat(lfe.id)
group_concat(lfe.name)
from a,
left join (
select c.id, c.name, b.aId
from b left join c on b.cId=c.id
where b.aId=123
) as lfc on lfc.aId=123
left join (
select e.id, e.name, d.aId
from d left join e on d.eId=e.id
where d.aId=123
) as lfe on lfe.aId=123
where a.id=123 group by a.id one problem with the data obtained by this method is that if table b has three pieces of data related to a, the data obtained from table d will be copied three times.
how can this writing be improved or can only be achieved through multiple query?