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

Solution 1:

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'))

Solution 2:

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 (selectdistinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2 from data) dates
    crossjoin (selectdistinct data.id_fk, data.plant_id_fk from data) up
    leftjoin 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'INNERJOIN plants pn ON up.plant_id_fk = pn.plant_id
    groupby up.id_fk, up.plant_id_fk, ym2, ym
    orderby 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

Solution 3:

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