0

I have multiple tables with data on some clients. What I want to achieve is to output the amount that a certain client was billed for, in the first month he was billed.

So I run the code similar to the one bellow:

SELECT company,clientid,COALESCE (signed.value,reactivated.value) as 'Activation' , Amount FROM `tblclients`
LEFT JOIN tblcustomfieldsvalues  as signed  ON tblclients.clientid = signed.relid and signed.fieldid = 5
LEFT JOIN tblcustomfieldsvalues  as reactivated ON tblclients.userid = reactivated.relid and reactivated.fieldid = 27
LEFT JOIN (
    SELECT clientid,sum(total) as Amount FROM tblinvoices  
    WHERE month(invoicedate)=month(Activation) Group by clientid) as f on tblclients.clientid = f.clientid

My problem is that when I do the last join it gives an error : Unknown column 'Activation' in 'where clause'. If I switch that to current_date the rest of the query works.

Any idea on how to make this work ?

Later edit: I might have oversimplified the query, I also have a COALESCE

Mamut
  • 3
  • 4
  • You can't use the alias when joining. Try using your actual column name instead – dvo Sep 24 '19 at 13:01
  • You can't use aliases in `WHERE` clauses. Just replace `Activation` with it's definition i.e. `activated.value` – Nick Sep 24 '19 at 13:02
  • I also have a COALESCE so I cannot directly reference it. Since the 'Activation' column is from the join, i cannot directly reference it – Mamut Sep 24 '19 at 13:18

1 Answers1

0

You can try below -

SELECT company,clientid,activated.value as Activation , Amount 
FROM `tblclients`LEFT JOIN tblcustomfieldsvalues as signed ON tblclients.clientid = signed.relid and signed.fieldid = 5
LEFT JOIN 
( SELECT clientid,sum(total) as Amount 
  FROM tblinvoices Group by clientid
) as f on tblclients.clientid = f.clientid and month(invoicedate)=month(Activation)
Fahmi
  • 37,315
  • 5
  • 22
  • 31