7

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

  • 2
    Probably because it's nested too deep – Philᵀᴹ Jan 15 '16 at 8:48
  • @Phil Any idea how to get the reference working? – FMashiro Jan 15 '16 at 8:54
  • GROUP BY works "after" select (simplified a lot), so it sometimes does not see the columns you do not select (may depend on optimizations the planner does to your query etc.). So add , e.company_id after e.lastname. – jkavalik Jan 15 '16 at 9:01
  • @jkavalik I tried adding it to the SQL and running it but it still gives me the error "#1054 - Unknown column 'e.company_id' in 'where clause' " – FMashiro Jan 15 '16 at 9:15
  • @FMashiro ah, sorry, I got distracted by one different error I once got.. – jkavalik Jan 15 '16 at 9:19
4

The cause of the problem was identified by @Phil in the comments:

Probably because it's nested too deep

You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL.

Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have to be turned into uncorrelated (in MySQL. In other DBMS, you could use a LATERAL join or the similar OUTER APPLY.

A first rewrite to get the job done:

SELECT 
    CONCAT_WS(
        ', ', count(DISTINCT CONCAT(q.emailaddress, '_', e.id)),
        dv.OneTimeItems
    ) 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 
LEFT JOIN
    (
        SELECT company_id,
               GROUP_CONCAT(items SEPARATOR '; ') AS OneTimeItems
        FROM ( 
            SELECT oi.company_id,
                   CONCAT_WS(
                ': ', oi.item_name, SUM(oi.item_amount)
            ) items 
            FROM onetime_item oi 
            WHERE oi.date BETWEEN '2015-12-01'
                              AND LAST_DAY('2015-12-01') 
            GROUP BY oi.company_id, oi.item_name 
        ) resulta
        GROUP BY company_id
    ) AS dv
    ON dv.company_id = e.company_id
WHERE 1=1 
AND YEAR(q.created_at) = '2015'
AND MONTH(q.created_at) = '12' 
GROUP BY e.company_id ;

Test in SQLfiddle.


Unrelated to the issue comments:

  • There is GROUP BY e.company_id while the select list has e.id, LEFT(e.firstname, 1), e.lastname. All these will give arbitrary result from a (more or less random) employee for each company - or even in extremely rare cases arbitrary results from 2 or 3 different employees! MySQL allowed (before 5.7) such bad use of group by that could cause erroneous results. It has been fixed in 5.7 and the default settings would reject this query.
  • The condition:

    YEAR(created_at) = '2015' AND MONTH(created_at) = '12'
    

    cannot make use of indexes. It's better to rewrite with either BETWEEN if the column is of DATE type of with an inclusive-exclusive range condition, which works flawlessly with any datetime type (DATE, DATETIME, TIMESTAMP) of any precision:

    -- use only when the type is DATE only
    date BETWEEN '2015-12-01' AND LAST_DAY('2015-12-01')
    

    or:

    -- use when the type is DATE, DATETIME or TIMESTAMP
    created_at >= '2015-12-01' AND created_at < '2016-01-01'     
    
  • This one doesn't work because company_id is reformatted as OneTimeItems in dv so you can't use it in the "ON clause" see error: #1054 - Unknown column 'dv.company_id' in 'on clause' – FMashiro Jan 15 '16 at 10:38
  • @FMashiro You have oi.date in the query but the pastebin has no date in that table. – ypercubeᵀᴹ Jan 15 '16 at 10:55
  • Updated the pastebin in the thread and here's the link once more, sorry for not including that pastebin.com/cCveVtGr – FMashiro Jan 15 '16 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.