多表联合与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);

+----------+------+------+------+
¦ f4 ¦ f1 ¦ f2 ¦ f3 ¦
+----------+------+------+------+
¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦
¦ 20021113 ¦ A ¦ B ¦ 02 ¦
¦ 20021114 ¦ A1 ¦ A2 ¦ 02 ¦
+----------+------+------+------+
3 rows in set (0.01 sec)