Geek asks and answers

How to join a table with this SQL code?

I use this wonderful code from @Richard aka cyberkiwi to run a query (it returns the amount valuefor 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

    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 plant_nameinstead plant_id.

table name: plants

id_fk    plant_id      plant_name
1        1             oak tree
1        2             cherry tree

Does anyone know where to insert a sentence that would create this join?

Thank!

0
source share
3 answers

Modified as below


       up.id_fk,
       p.plant_name,
       YM2,
       ifnull ( (data.value_1), 0) totalvalue_1
     (
       date_format (, '% Y-% m') ym, date_format (, '% M% Y') ym2
       )     - (
       data.id_fk, data.plant_id_fk
       )
     p p.plant_id = up.plant_id_fk
    
        on date_format (data.date, '% Y-% m') = date.ym
        up.id_fk = data.id_fk
        up.plant_id_fk = data.plant_id_fk
        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'))

+3

Hm. , ; record_id . , .

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

. pn.plant_name SELECT INNER JOIN, plant_id_fk.

, , SQL (SELECT, JOIN, GROUP ..), (UP, YM2, YM, DATES .. ..). .

www.medfocusrcm.com

+1

left join plants on data.id_fk=plants.id_fk and data.plant_id_fk=plants.plant_id

{up.id_fk, up.plant_id_fk} plant.plant_name

0

More articles:


All Articles