how to join after left join complex mysql queries

Question

I have this query

SELECT 
       currency_code, 
       SUM(CASE WHEN TYPE = 'buy'THEN to_amount END ) AS BUY, 
       SUM(CASE WHEN TYPE = 'sell' THEN to_amount END ) AS SELL, 
       SUM(CASE WHEN TYPE = 'sell' THEN rate END ) AS SELL_RATE, 
       SUM(CASE WHEN TYPE = 'buy' THEN rate END ) AS BUY_RATE, 
       AVG(CASE WHEN TYPE = 'buy' THEN rate END ) AS AVG_BUY_RATE, 
       AVG(CASE WHEN TYPE = 'sell' THEN rate END ) AS AVG_SELL_RATE
FROM tb_currency
LEFT JOIN tb_bill 
   ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
     AND tb_bill.TYPE  IN ('buy', 'sell')
     AND date( DATE_TIME ) >= '2011-01-01'
     AND date( DATE_TIME ) <= '2011-01-11'
GROUP BY currency_code

that will output this:

Right now i want to join this query with another table called tb_user

the tb_user have PK called user_id and the tb_bill that is use in the query above also have foreign key called user_id

tb_user

user_id (pk)| user_name | branch_id

tb_bill

bill_id (pk) | user_id (fk)|

Desired result should be the above picture plus one column branch_id. If it doesnt have branch_id, return null.

I tried several times but still cant join it correctly. Hope you guys can help.

Thanks.

Answer 1

The three conditions in the join (the AND clauses) might be giving you trouble. Those three conditions are selection criteria, not join criteria.

Also, your use of CASE looks odd to me. I'm sure it works, but IF might be better suited for a one-condition function. In the below, if the fields are floating point rather than integer then replace the 0 with 0.0.

SELECT currency_code, 
    SUM(IF(TYPE = 'buy', to_amount, 0)) AS BUY,
    SUM(IF(TYPE = 'sell', to_amount, 0)) AS SELL, 
    SUM(IF(TYPE = 'sell', rate, 0)) AS SELL_RATE,
    SUM(IF(TYPE = 'buy', rate, 0)) AS BUY_RATE,
    AVG(IF(TYPE = 'buy', rate, 0)) AS AVG_BUY_RATE,
    AVG(IF(TYPE = 'sell', rate, 0)) AS AVG_SELL_RATE,
    tb_user.whatever_field,
    tb_user.whatever_other_field
FROM tb_currency
    LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
    LEFT JOIN tb_user ON tb_bill.user_id = tb_user.user_id
WHERE tb_bill.TYPE IN ('buy', 'sell')
    AND date( DATE_TIME ) >= '2011-01-01'
    AND date( DATE_TIME ) <= '2011-01-11'
GROUP BY currency_code, tb_user.user_id

Finally, all-cap field names look odd to my eye as well. Whatever works for you though.

Answer 2

add user_id to SELECT part after

LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID

place

LEFT JOIN tb_user ON tb_user.id = tb_bill.user_id

also you missing WHERE ( put instead first AND ) and

GROUP BY currency_code, user_id