Tree Like Data Collation In Sql (mysql)

I have two tables in my database Table A with columns user_id, free_data, used_data Table B with columns donor_id, receptor_id, share_data Basically, a user (lets call x) has som

Solution 1:

Yes its possible using a nested set model. There's a book by Joe Celko that describes but if you want to get straight into it there's an article that talks about it. Both the collated data that you need can be retrieved by a single select statement like this:

SELECT*FROM TableB whereleft> some_value1 andright< some_value2

In the above example to get all the child nodes of "Portable Electronics" the query will be:

SELECT*FROM Electronics WHERE `left` >10and `right` <19

The article describes how the left and right columns should be initialised.

Solution 2:

If I understand the problem correctly, the following should give you the desired results:

SELECT B.donor_id AS donor_id, SUM(A.used_data) AS total_used_data FROM A
INNER JOIN B ON A.user_id = B.receptor_id GROUPBY B.donor_id;

Solution 3:

Hope this will solve your problem now.

Try below query(note that you will have to pass userid at 2 places):

SELECTSUM(share_data) as total_donated, sum(used_data) as total_used FROM tablea
LEFTJOIN tableB
ON tableA.user_id = tableB.donor_id
WHERE user_id IN (select  receptor_id as id 
from    (select*from tableb
     orderby donor_id, receptor_id) u_sorted,
    (select@pv :='1') initialisation
where   find_in_set(donor_id, @pv) >0and@pv := concat(@pv, ',', receptor_id)) OR user_id =1;