Q:

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 a view with the tree tables
this is the view that i 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 the query
SELECT t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,
FROM  `table1` t1
INNER JOIN  `table2` t2 ON t1.`name` = t2.`name` 
ORDER BY  `t1`.`date` DESC

But i don't know how do the view with the three tables and the other problems is the records of the tree because i have tree "a1" and "a1Si" and "a2NO"  and in the view i need relate the 3 columns

A:

You should be able to use the following which JOINS table3 on the t2.tree and the left two characters of the t3.tree: 
SELECT t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,
  max(case when right(t3.tree, 2) = 'SI' then t3.users end) usersSI,
  max(case when right(t3.tree, 2) = 'NO' then t3.users end) usersNO
FROM  `table1` t1
INNER JOIN  `table2` t2 
  ON t1.`name` = t2.`name` 
INNER JOIN `table3` t3
  on t2.tree = left(t3.tree, 2)
group by t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`
ORDER BY  `t1`.`date` DESC;

See SQL Fiddle with Demo.
This could also be done using multiple JOINs on table3:
SELECT t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,
  t3SI.users usersSI,
  t3NO.users usersNO
FROM  `table1` t1
INNER JOIN  `table2` t2 
  ON t1.`name` = t2.`name` 
LEFT JOIN `table3` t3SI
  on t2.tree = left(t3SI.tree, 2)
  and right(t3SI.tree, 2) = 'SI'
LEFT JOIN `table3` t3NO
  on t2.tree = left(t3NO.tree, 2)
  and right(t3NO.tree, 2) = 'NO'
ORDER BY  `t1`.`date` DESC;

See SQL Fiddle with Demo. You will notice that I altered the last two joins to use a LEFT JOIN in the event a tree does not exist that matches the SI or NO, then you will return data. If you know that you will have matching data, then you can use an INNER JOIN.
If you have tree names that are longer, then you could implement the LIKE clause in the JOIN:
SELECT t1.`date` , t1.`name` , t2.`tree` , t1.`total` , t1.`recieve`,
  t3SI.users usersSI,
  t3NO.users usersNO
FROM  `table1` t1
INNER JOIN  `table2` t2 
  ON t1.`name` = t2.`name` 
LEFT JOIN `table3` t3SI
  on t3SI.tree like concat(t2.tree, '%')
  and right(t3SI.tree, 2) = 'SI'
LEFT JOIN `table3` t3NO
  on t3NO.tree like concat(t2.tree, '%')
  and right(t3NO.tree, 2) = 'NO'
ORDER BY  `t1`.`date` DESC;

See SQL Fiddle with Demo

