MySQL – Fix 1054 Unknown Column in Subquery Error

group byjoin;MySQLphpmyadmin

i have 2 table one store data of relation user other buy user

path table

+-------------+---------------+-------------+
| ancestor_id | descendant_id | lenght       |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           1 |
|           1 |             4 |           2 |
|           1 |             5 |           3 |
|           1 |             6 |           4 |
|           2 |             2 |           0 |
|           2 |             4 |           1 |
|           2 |             5 |           2 |
|           2 |             6 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           4 |             5 |           1 |
|           4 |             6 |           2 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           6 |             6 |           0 |
+-------------+---------------+-------------+

this is table buy

+--------+--------+
| userid | amount |
+--------+--------+
|      2 |   2000 |
|      4 |   6000 |
|      6 |   7000 |
|      1 |   7000 |

algoritm =
1- select and group buy for each user at last 1000 for descendant
2- join to path table with descendant_id = result step1
3- again select and group buy for each user at last 1000 for ancestor
4- must get min path_length Except zero According to join in step 2 with ancestor have at last 1000 , aslo if just have one path_length set to null

this mysql code i need a where compare path_length with have ancestor 

SELECT a.* 
FROM
    ( SELECT userid 
       FROM webineh_user_buys 
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_d 

JOIN
    webineh_prefix_nodes_paths AS a 
    ON a.descendant_id = buys_d.userid

 JOIN  
    (
        SELECT userid  
        FROM webineh_user_buys 
        GROUP BY userid
        HAVING SUM(amount) >= 1000
    ) AS buys_a on (a.ancestor_id = buys_a.userid )


  JOIN 
     ( SELECT descendant_id
            , MAX(path_length) path_length 
         FROM webineh_prefix_nodes_paths 
         where a.ancestor_id = ancestor_id
        GROUP
           BY descendant_id
     ) b
    ON b.descendant_id = a.descendant_id
   AND b.path_length = a.path_length

 GROUP BY a.descendant_id, a.ancestor_id

Intended result

 +--------+--------++--------++------
| descendant | ancestor | path_length
+--------+--------++--------++-------
|     1      |   null   |    null
|     2      |   1      |      1 
|     4      |   2      |      1
|     6      |   4      |      2

but have error please see sqlfidle
Unknown column 'a.ancestor_id' in 'where clause'

Best Answer

Try this

SELECT a.descendant_id
    , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id
    , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length
FROM webineh_prefix_nodes_paths AS a 
JOIN (
    SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path
    FROM (
        SELECT a.* 
        FROM (
            SELECT userid  
            FROM webineh_user_buys 
            GROUP BY userid
            HAVING SUM(amount) >= 1000
            ) AS buys_d 
        JOIN
            webineh_prefix_nodes_paths AS a 
        ON a.descendant_id = buys_d.userid
        JOIN (
            SELECT userid  
            FROM webineh_user_buys 
            GROUP BY userid
            HAVING SUM(amount) >= 1000
            ) AS buys_a 
        ON a.ancestor_id = buys_a.userid 
        ) tmp2
    GROUP BY descendant_id 
    ) td
ON a.descendant_id = td.descendant_id
       AND a.path_length = CASE td.min_path WHEN 1000 THEN 0 ELSE td.min_path END ;
Related Question