How to get names form 3 different tables

Question

I have 4 tables to proceed. In table A I have the parameters of columns in tables B,C,D and the names are there.

                  TB_A
+-----------+-------+-----+-----+----------+------------------+
| id |prodid|status |stat | qty |   Date   |      prodname    |  
+-----------+-------+-----+-----+----------+------------------+
|  1 |   1  |   0   |  2  |  80 |2013/11/20|     itemb1       |
+----+------+ ------+-----+-----+----------+------------------+     
|  2 |   1  |   1   |  2  |  20 |2013/11/21|     itemb1       |
+----+------+-------+-----+-----+----------+------------------+
|  3 |   1  |   2   |  2  |  5  |2013/11/22|     itemb1       |
+----+------+-------+-----+-----+----------+------------------+
|  4 |   1  |   0   |  3  | 100 |2013/11/21|     itemc1       | 
+----+------+-------+-----+-----+----------+------------------+
|  5 |   1  |   0   |  4  | 120 |2013/11/22|     itemd1       |
+-----------+-------+-----+-----+----------+------------------+

       TB_B                             TB_C                      TB_D
+----+------+--------+----+   +----+------+-------+---+    +----+------+--------+---+ 
| id | name | TB_A_id|qty |   | id |name  |TB_A_id|qty|    | id |name  |TB_A_id |qty|
+----+------+----- --+----+   +----+------+-------+---+    +----+------+--------+---+
|  1 |itemb1|   1    | 80 |   |  1 |itemc1|   4   |100|    |  1 |itemd1|   5    |120|
+----+------+--------+----+   +----+------+-------+---+    +----+------+--------+---+


I want obtain this

+----------+------+------+-----+
|   Date   | Name | Prod | Rest|
+----------+------+------+-----+-
|2013/11/22|itemd1| 120  | 120 |
|2013/11/21|itemc1| 100  | 100 |
|2013/11/20|itemb1|  80  |  55 | ->(qty - (SUM of TB_A ID (2-3))  if status>0
+----------+------+------+-----+

I have this query which does that but only with a Table TB_B or TB_C or TB_D

SELECT tb.id, tb.nom, sd.date, tb.qty, tb.prodid,
SUM(IF((status>0 AND tb.id=sd.prodid),sd.qty,0)) As rq
FROM TB_B AS tb 
JOIN TB_A AS sd  ON (tb.id = sd.prodid AND sd.stat = 2) 
GROUP  BY tb.id, tb.nom, tb.date, tb.qty, tb.prodid 
ORDER BY date DESC LIMIT @Myid,PublicVariables.MySqlLimit

Is there a possibility to do with 3 tables ?

SELECT tb.name, pm.name, pg.name, tb.proddate, tb.qty, (tb.qty-SUM(IF((status>0 AND 
 tb.id=sd.prodid AND sd.stat =3),sd.qty,0))) As rq, pg.qty, (pg.qty-SUM(IF((status>0 AND 
 pg.id=sd.prodid AND sd.stat=4),sd.qty,0))) AS rq1, pm.qty, (pm.qty- SUM(IF((status>0 AND

 pg.id=sd.prodid AND sd.stat =2),sd.qty,0))) AS rq2 FROM TB_B AS tb

JOIN TB_A AS sd ON (sd.prodid = tb.id AND sd.stat >1) 
LEFT JOIN TB_C AS pm ON pm.id =sd.prodid
INNER JOIN TB_D AS pg ON pg.id =sd.prodid
GROUP BY pm.id,tb.id,pg.id ORDER BY sd.date DESC

My query above gives

But I want this result. Real data is as this

    name                 qty  rq   date
-----------------------------------------
Macaron Orange         | 0 |  0 |2013/11/24
Macaron Roquefort      | 20| 20 |2013/11/24  
Macaron Citron Vert    | 20| 20 |2013/11/21   
Ganache Citron Vert    |200|120 |2013/11/21  
Biscuit Moutarde       |100| 60 |2013/11/20    
Ganache Orange         |200|200 |2013/11/20  
Chevre                 |200|120 |2013/11/20  
Biscuit Chocolat       |120| 50 |2013/11/19  
Ganache Citron Vert    |200|200 |2013/11/19  
Biscuit Pain d'Epice   |120|120 |2013/11/18  
Biscuit Chocolat       |120|120 |2013/11/17 

Answer 1

Finally as I did not see any hope I inserted a column "prodname" and with this sql I got the result. But any help will be welcome this add of prodname makes my TB_A table a little bulky.

Here is the SQL

SELECT sd.prodname, sd.date, 
SUM(IF((sd.status=0 AND (sd.prodid=pb.id OR sd.prod.id=pm.id OR sd.prodid =pg.id)), sd.qty,0)) AS tot,
SUM(IF((sd.status >0 AND (sd.prodid=pb.id OR sd.prodid=pm.id OR sd.prodid=pg.id)), sd.qty, 0)) AS rq
FROM TB_A AS sd
LEFT JOIN TB_B AS pb ON pb.id=sd.prodid
LEFT JOIN TB_C AS pm ON pm.id=sd.prodid
LEFT JOIN TB_D AS pg ON pg.id=sd.prodid
WHERE sd.stat>1
GROUP BY pm.id,pm.id,pg.id,sd.prodname  
ORDER BY sd.date DESC

PS:In my display of my program I don't display rq I display tot-rq within a program. Any suggestion for getting that in this query is welcome.