I have query join in select statement like this :
select a.item_number, total_quantity, store, factory
from (
select item_number, sum(quantity) as "total_quantity"
from `item_details`
group by item_number
) `a`
left join (
select item_number, sum(quantity) as 'store'
from `item_details` where location_code = 'STORE'
group by item_number
) `b` on `a`.`item_number` = `b`.`item_number`
left join (
select item_number, sum(quantity) as 'factory'
from `item_details`
where location_code = 'FACTORY'
group by item_number
) `c` on `a`.`item_number` = `c`.`item_number`
order by `item_number` asc
From the query above, it appears if I use table item_details
with fields id, item_no, quantity and location_code
If the query executed, the result like this :
The results are correct. But here I want to create field store and factory to be dynamic. So it's taken from table locations. Because the data location is dynamic. It can be added and removed
So I have table locations with field id and description like this :
field location_code in the item_details table is foreign key to field id in locations table
So how to create select dynamic fields from location table?
Note :
I use "query join in select statement" because I didn't have table locations before. Now I use table locations. Because the data in the location table is dynamic. It can be added and removed. So I want to display it like table 1 above with table location. Seems it need to join the table. But i'm still confused to do it
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…