1

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'

  • When a query is executed, sub-queries are executed first so when u call an external reference it will be unknown because it not recognize yet. – Yassine LAADIDI Jun 19 '16 at 13:49
  • how i recognize ? – Vahid Alvandi Jun 19 '16 at 13:50
1

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 ;

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.