多表联合与MAX()结合的复杂查询

表1
材料 规格  数量  单号
--------------------
A      B    1    20021112
A1     A2    2   20021114

表2
材料 规格  数量
--------------------
A      B    2    20021113   

表3
id   单号
-------------------
1     20021112
2     20021113

我想从表1,表2,表3联合查询

在表3 单号最大的
表1或者是表2中的数材料数量
表1的材料 规格 与表2的相同

---------------------------------------------------------------

CREATE TABLE `t1` (
  `f1` char(2) default NULL,
  `f2` char(2) default NULL,
  `f3` tinyint(1) unsigned default NULL,
  `f4` int(8) unsigned default NULL,
  KEY `f1` (`f1`)
) TYPE=MyISAM

INSERT INTO t1 (f1, f2, f3, f4) VALUES ('A', 'B', 1, 20021112);
INSERT INTO t1 (f1, f2, f3, f4) VALUES ('A1', 'A2', 2, 20021114);

CREATE TABLE `t2` (
  `f1` char(2) default NULL,
  `f2` char(2) default NULL,
  `f3` tinyint(1) unsigned default NULL,
  `f4` int(8) unsigned default NULL,
  KEY `NewIndex` (`f1`)
) TYPE=MyISAM

INSERT INTO t2 (f1, f2, f3, f4) VALUES ('A', 'B', 2, 20021113);

CREATE TABLE `t3` (
  `id` tinyint(3) unsigned NOT NULL default '0',
  `f1` int(8) unsigned default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM

INSERT INTO t3 (id, f1) VALUES (1, 20021112);
INSERT INTO t3 (id, f1) VALUES (2, 20021113);
INSERT INTO t3 (id, f1) VALUES (3, 20021114);

SELECT  LEFT(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),8) AS f4,
        IFNULL(t1.f1,t2.f1) AS f1,
        IFNULL(t1.f2,t2.f2)AS f2,
        SUBSTRING(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),9)AS f3 
        FROM t3 LEFT JOIN t1 ON t3.f1 = t1.f4 
        LEFT JOIN t2 ON t3.f1 = t2.f4 
        GROUP BY 2;
        
+----------+------+------+------+
| f4       | f1   | f2   | f3   |
+----------+------+------+------+
| 20021113 | A    | B    | 02   |
| 20021114 | A1   | A2   | 02   |
+----------+------+------+------+
2 rows in set (0.01 sec)        

要求 t3 为主表, 在 t3 中不能有"单号"多与其它二个表的总和
否则会出现如下情况

INSERT INTO t3 (id, f1) VALUES (4, 20021115);

SELECT  LEFT(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),8) AS f4,
        IFNULL(t1.f1,t2.f1) AS f1,
        IFNULL(t1.f2,t2.f2)AS f2,
        SUBSTRING(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),9)AS f3 
        FROM t3 LEFT JOIN t1 ON t3.f1 = t1.f4 
        LEFT JOIN t2 ON t3.f1 = t2.f4 
        GROUP BY 2;
        
+----------+------+------+------+
| f4       | f1   | f2   | f3   |
+----------+------+------+------+
| NULL     | NULL | NULL | NULL |
| 20021113 | A    | B    | 02   |
| 20021114 | A1   | A2   | 02   |
+----------+------+------+------+
3 rows in set (0.01 sec)