I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" which are manually added items in a different table.
I currently have this SQL statement (I'm using MySQL):
SELECT CONCAT_WS( ', ', count(DISTINCT CONCAT(emailaddress, '_', e.id)), ( SELECT GROUP_CONCAT(items SEPARATOR '; ') as OneTimeItems FROM ( SELECT CONCAT_WS( ': ', oi.item_name, SUM(oi.item_amount) ) items FROM onetime_item oi WHERE oi.company_id = e.company_id AND oi.date BETWEEN '2015-12-01' AND LAST_DAY('2015-12-01') GROUP BY oi.item_name ) resulta ) ) as AllItems, e.id, LEFT(e.firstname, 1) as voorletter, e.lastname FROM question q LEFT JOIN employee e ON q.employee_id = e.id WHERE 1=1 AND YEAR(created_at) = '2015' AND MONTH(created_at) = '12' GROUP BY e.company_id
Now I get the following error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.company_id' in where clause
The dates used are dummy dates and the 1=1 at the bottom where clause is because I'm generating the where statement based on user input values.
All columns DO exist in the table employee and the left join works ( I tried entering an id manually instead of using the column reference and it worked, I got the right result back)
I'm also sorry but I'm not allowed to post a table schema or anything related to the DB structure online.
Any idea as to why the reference to e.company_id fails?
EDIT: This is the result set I need:
5, Het is je verjaardag: 1; Skivakantie: 1; Telefonische leadvergoeding: 8
The construction is as follows: 5 = leads comma separated to get the rest. Leads are the unique combination from the Questions table, and the rest of the results are constructed from onetime_items.
EDIT 2: SQL fiddle keeps throwing an error at me so I'm just going to post a small made up DB with a little bit of made up data: http://pastebin.com/cCveVtGr