0
SELECT t1.`ID`, t1.`notification_type`, t1.`notification_by`, t1.`notification_by_username`, 
                t1.`notification_status`, t1.`notification_date`, t1.`school_key`, t1.`class_key`, t1.`post_key`, t1.`extra`, t1.`class_info`, t1.`class_subject`,
                (SELECT `notification_last_check` 
                       FROM data_users.account_info t2
                       WHERE t2.`user_key` = t1.`notification_by` LIMIT 1) AS `notification_last_check` 
       FROM `14754931095281411` t1 
       WHERE t1.`notification_status` = '10' 
                AND t1.`notification_date` > t2.`notification_last_check`   
       ORDER BY `notification_date` DESC LIMIT 10;

1054 - Unknown column 't2.notification_last_check' in 'where clause'

why im getting the above error ?

AxelH
  • 14,325
  • 2
  • 25
  • 55
Saef Myth
  • 287
  • 8
  • 19

2 Answers2

0

t2 exist only in the sub-query scope (the query in your select).

So here you don't have t2.notification_last_check but only notification_last_check

SELECT ...,
            (SELECT `notification_last_check` 
                   FROM data_users.account_info t2
                   WHERE t2.`user_key` = t1.`notification_by` LIMIT 1) AS `notification_last_check` 
   FROM `14754931095281411` t1 
   WHERE t1.`notification_status` = '10' 
            AND t1.`notification_date` > `notification_last_check`  -- Remove the alias t2
   ORDER BY `notification_date` DESC LIMIT 10;

Then of course, I should mention that this will failed because you can't use an alias in WHERE clause. Only in GROUPB BY, ORDER BY and HAVING. You could use HAVING to set this condition, read about this one if you don't know it already.

And other solution would be to use a inner join (a small change is needed)

SELECT ...,
       t3.`notification_last_check` 
   FROM `14754931095281411` t1 
   LEFT JOIN (
         SELECT DISTINCT t2.`user_key`, `notification_last_check` 
                  FROM data_users.account_info t2 
         ) t3 On t3.`user_key` = t1.`notification_by
   WHERE t1.`notification_status` = '10' 
   AND t1.`notification_date` > t3.`notification_last_check` 
   ORDER BY t1.`notification_date` DESC LIMIT 10;
AxelH
  • 14,325
  • 2
  • 25
  • 55
  • #1054 - Unknown column 'notification_last_check' in 'where clause' – Saef Myth Jan 20 '17 at 09:02
  • @SaefMyth My bad, forgot to precise that you can't use an alias in a `WHERE`, only in `GROUP BY`, `ORDER BY` and `HAVING`, the last one could do it, but read about it – AxelH Jan 20 '17 at 09:07
  • @SaefMyth, I have write the join solution if you want to try this. This might have some different performance (in good or bad) – AxelH Jan 20 '17 at 10:33
  • I tried it your query took >> 0.0010 seconds. the sub-query took >> 0.0006 seconds. on 20 record table. – Saef Myth Jan 20 '17 at 11:04
  • @SaefMyth Well, since this took only 1ms, I would not fear performance ;) and this difference is not relevant enough since this could be cause by the thread scheduler of the system. But this will depends on the rows number. – AxelH Jan 20 '17 at 11:07
  • i think your query will be effective when the rows more than 1000 ,, but for my requirement probably user will not have 1000 notification at the moment ,, i will consider this solution when user have more records to query from. thanks anyway – Saef Myth Jan 20 '17 at 11:14
  • @SaefMyth If you have time, just use a loop to generate fake data. But the solutions work fine, use the query that's the more readable to futur maintenance. – AxelH Jan 20 '17 at 11:17
0

A possible solution:

SELECT
    t1.`ID`, t1.`notification_type`, t1.`notification_by`,
    t1.`notification_by_username`, 
    t1.`notification_status`, t1.`notification_date`, t1.`school_key`,
    t1.`class_key`, t1.`post_key`, t1.`extra`, t1.`class_info`,
    t1.`class_subject`,
    (SELECT `notification_last_check` 
    FROM data_users.account_info t2
    WHERE t2.`user_key` = t1.`notification_by` LIMIT 1) AS `notification_last_check` 
FROM `14754931095281411` t1 
WHERE t1.`notification_status` = '10' 
AND t1.`notification_date` > (SELECT `notification_last_check` 
    FROM data_users.account_info t3
    WHERE t3.`user_key` = t1.`notification_by` LIMIT 1) 
ORDER BY `notification_date` DESC LIMIT 10;
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
  • this is the easiest answer – Saef Myth Jan 20 '17 at 09:12
  • I don't find this clean. You are doing twice the same select, this could be bad to performance. A join would be simpler. – AxelH Jan 20 '17 at 10:21
  • @AxelH: But with JOIN operation you must apply a DISTINCT because the occurence of account_info are more than one. So distinct is very weight – Joe Taras Jan 20 '17 at 10:23
  • Only a DISTINCT on the sub query used to do the join. I find it to be more efficient in general to return a distinct result of 1k row instead of doing 1k select call (2k in your case). – AxelH Jan 20 '17 at 10:26
  • I have write the left join in my answer, but this depends on the number of line in general. – AxelH Jan 20 '17 at 10:32