How to join a table to this SQL code?
I am using this awesome code from @Richard aka cyberkiwi to run a query (it returns the sum of value
for each month, for each plant):
table name: data
record_id id_fk plant_id_fk date value category_1
1 1 1 2011-03-01 10 A
2 1 1 2011-03-02 10 A
3 1 1 2011-04-10 5 B
4 1 2 2011-04-15 5 C
SQL code
开发者_Python百科 select up.id_fk, up.plant_id_fk, ym2, ifnull(sum(data.value_1),0) totalvalue_1
from (select distinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2 from data) dates
cross join (select distinct data.id_fk, data.plant_id_fk from data) up
left join data on date_format(data.date, '%Y-%m') = dates.ym
and up.id_fk=data.id_fk
and up.plant_id_fk=data.plant_id_fk
and category_1='A'
group by up.id_fk, up.plant_id_fk, ym2, ym
order by up.id_fk, up.plant_id_fk, date(concat(ym,'-1'))
Now I need to join this with the following table in order to run a PHP loop that will retrieve the plant_name
instead of the plant_id
.
table name: plants
id_fk plant_id plant_name
1 1 oak tree
1 2 cherry tree
Anyone know where to insert the clause that would create this join?
Thanks!
Modified as shown below
select
up.id_fk,
p.plant_name,
ym2,
ifnull(sum(data.value_1),0) totalvalue_1
from (
select distinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2
from data) dates
cross join (
select distinct data.id_fk, data.plant_id_fk
from data) up
inner join plants p on p.plant_id = up.plant_id_fk
left join data
on date_format(data.date, '%Y-%m') = dates.ym
and up.id_fk=data.id_fk
and up.plant_id_fk=data.plant_id_fk
and category_1='A'
group by up.id_fk, up.plant_id_fk, ym2, ym, p.plant_name
order by up.id_fk, up.plant_id_fk, date(concat(ym,'-1'))
Hm. I don't think the recordset and the query are matching; I don't see the field record_id from the fieldset. I will adjust the query you provided.
select up.id_fk, up.plant_id_fk, pn.plant_name, ym2, ifnull(sum(data.value_1),0) totalvalue_1
from (select distinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2 from data) dates
cross join (select distinct data.id_fk, data.plant_id_fk from data) up
left join data on date_format(data.date, '%Y-%m') = dates.ym
and up.id_fk=data.id_fk
and up.plant_id_fk=data.plant_id_fk
and category_1='A'
INNER JOIN plants pn ON up.plant_id_fk = pn.plant_id
group by up.id_fk, up.plant_id_fk, ym2, ym
order by up.id_fk, up.plant_id_fk, date(concat(ym,'-1'))
This should work. I added the pn.plant_name to the SELECT area, and used a INNER JOIN to join the plants table to the up selection for the field plant_id_fk.
As an aside, it makes it a lot easier to read if the SQL elements are capitalized (SELECT, JOIN, GROUP etc) as well as your table definitions (UP, YM2, YM, DATES etc etc). This is just my personal preference.
Nishan Karassik www.medfocusrcm.com
Just before the group by
left join plants on data.id_fk=plants.id_fk and data.plant_id_fk=plants.plant_id
and then replace in the group by and select {up.id_fk, up.plant_id_fk} by plant.plant_name
精彩评论