0

I got two tables

org 

//  org_id  //  parent_id  //  org_name  //  mid  //
      1000              1         apple     1111
      1001              1        google     2222
  10000001           1000       unicooo     3333
  10010001           1001          uber     4444

trans 

//  money  //       date   //  mid  //
     1000      2015-10-1      1111
      201      2015-10-1      2222
      201      2015-10-1      3333
     1001      2015-10-1      4444 
     2000      2015-10-2      1111
      201      2015-10-2      2222
      201      2015-10-2      3333
     1001      2015-10-2      4444

I join two table using mid and I want to get output like

//  org_name  //  sum_money  //    date  //
       apple           1201   2015-10-1
       apple           2201   2015-10-2  
      google           1202   2015-10-1
      google           1202   2015-10-2   
     unicooo            201   2015-10-1
     unicooo            201   2015-10-2   
        uber           1001   2015-10-1
        uber           1001   2015-10-2

for example

//  org_name  //  sum_money  //    date  //
       apple           1201   2015-10-1

apple got 1000 in 10-1 and unicooo which its parent_id is apple's id got 201 in 10-1, so apple got 1201 on 10-1.And even apple and its 'child' got zero on 10-1, I still want to display it,and set sum_money to zero.

I tried 

select sum(a.money) from trans a right join 
(select b.mid from org b where b.parend_id = '1') as c
on a.mid = c.mid group by a.date

luckily, I got what I want for 'apple',but I want to select all column,but don't know how to do with it.Two or more sql query are also welcome.

Windsooon
  • 6,864
  • 4
  • 31
  • 50
  • 1
    what you want please explain more ??? @aison – Abhishek Sharma Nov 10 '15 at 08:55
  • mysql doesn't support recursion... you should research it first – John Ruddell Nov 10 '15 at 09:03
  • Any way to make this work? – Windsooon Nov 10 '15 at 09:03
  • @Rahautos get all the 'money' by its 'org_id' and its 'child'. – Windsooon Nov 10 '15 at 09:05
  • With the current structure you would need to do this with multiple queries executed recursively. If you can change the table structure then you might be able to do this with the nested set model. – Kickstart Nov 10 '15 at 09:07
  • I can't change the table structure, Can I use two or more sql query to make it work? – Windsooon Nov 10 '15 at 09:08
  • Do you need to recurse forever (parent, grandparent, etc), or just look at the company and the parent company? If the latter, I think you're OK (and I can probably help). If the former, I don't think I can help, and it may only be possible using a non-sql solution (like a php loop) because of what @JohnRuddell says. – Chris Lear Nov 10 '15 at 09:44
  • Thanks @ChrisLear, But I need the former one. – Windsooon Nov 10 '15 at 09:47
  • You might get some help from http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – Chris Lear Nov 10 '15 at 09:54
  • You might also get help from http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html (the part about the stored procedure especially) – Chris Lear Nov 10 '15 at 10:02

0 Answers0