sql - View in MYSQL with 3 tables -

i have 3 tables in mysql

table 1 date        name   total recieve 2013-05-09  "aa"    20    15 2013-05-09  "bb"    10    17  table 2 name    tree "bb"     "a1" "aa"     "a2"  table 3 date          tree      users 2013-05-09   "a1si"      19 2013-05-09   "a1no"      24 2013-05-09   "a2si"      39 2013-05-09   "a2no"      22 

i need view tree tables

this view need:

date         name   tree   total    recieve    usersi    userno   2013-05-09   "aa"   "a2"    20        15        39         22 2013-05-09   "bb"   "a1"    10        17        19         24 

i have half of query

select t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,  `table1` t1 inner join  `table2` t2 on t1.`name` = t2.`name`  order  `t1`.`date` desc 

but don't know how view 3 tables , other problems records of tree because have tree "a1" , "a1si" , "a2no" , in view need relate 3 columns

you should able use following joins table3 on t2.tree , left 2 characters of t3.tree:

select t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,   max(case when right(t3.tree, 2) = 'si' t3.users end) userssi,   max(case when right(t3.tree, 2) = 'no' t3.users end) usersno  `table1` t1 inner join  `table2` t2    on t1.`name` = t2.`name`  inner join `table3` t3   on t2.tree = left(t3.tree, 2) group t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve` order  `t1`.`date` desc; 

see sql fiddle demo.

this done using multiple joins on table3:

select t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,   t3si.users userssi,   t3no.users usersno  `table1` t1 inner join  `table2` t2    on t1.`name` = t2.`name`  left join `table3` t3si   on t2.tree = left(t3si.tree, 2)   , right(t3si.tree, 2) = 'si' left join `table3` t3no   on t2.tree = left(t3no.tree, 2)   , right(t3no.tree, 2) = 'no' order  `t1`.`date` desc; 

see sql fiddle demo. notice altered last 2 joins use left join in event tree not exist matches si or no, return data. if know have matching data, can use inner join.

if have tree names longer, implement clause in join:

select t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,   t3si.users userssi,   t3no.users usersno  `table1` t1 inner join  `table2` t2    on t1.`name` = t2.`name`  left join `table3` t3si   on t3si.tree concat(t2.tree, '%')   , right(t3si.tree, 2) = 'si' left join `table3` t3no   on t3no.tree concat(t2.tree, '%')   , right(t3no.tree, 2) = 'no' order  `t1`.`date` desc; 

see sql fiddle demo