I have a simple one two many relationship.
There are three tables
Main:
ID TITLE
________
1 Peter
2 Lars
Orders:
SKU MAIN_ID
___________
RFX 1
HNI 2
RRP 2
Tools:
NAME MAIN_ID
____________
FORK 1
KNIFE 1
SPOON 2
So orders and tools hava a MAIN_ID which refers to the Main table. So Peter has the order RFX and the tools FORK and SPOON Lars has the orders HNI and RRP and the tool SPOON.
How can I do a single query to find out which orders and tools peter has and which ones lars has? I tried it with an inner join but then there are duplicate entries.
You probably want to use group_concat() to get the values in one row. However, you need to pre-aggregate the data before the join:
select m.*, o.skus, t.tools
from main m join
(select main_id, group_concat(sku) as skus
from orders
group by main_id
) o
on o.main_id = m.id join
(select main_id, group_concat(name) as tools
from tools
group by main_id
) t
on t.main_id = m.id;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments