CREATE TABLE `t1` ( `col_int_nokey` int(11) NOT NULL ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO `t1` VALUES (2); INSERT INTO `t1` VALUES (0); INSERT INTO `t1` VALUES (8); INSERT INTO `t1` VALUES (5); INSERT INTO `t1` VALUES (2); INSERT INTO `t1` VALUES (33); INSERT INTO `t1` VALUES (5); INSERT INTO `t1` VALUES (1); INSERT INTO `t1` VALUES (9); INSERT INTO `t1` VALUES (1); INSERT INTO `t1` VALUES (3); INSERT INTO `t1` VALUES (8); INSERT INTO `t1` VALUES (231); CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_nokey` int(11) NOT NULL, `col_int_key` int(11) NOT NULL, `col_int_key2` varchar(1) NOT NULL, PRIMARY KEY (`pk`), KEY `col_int_key` (`col_int_key`), KEY `col_int_key2` (`col_int_key2`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; INSERT INTO `t2` VALUES (3,6,1,1); INSERT INTO `t2` VALUES (4,7,0,20); INSERT INTO `t2` VALUES (5,0,1,30); INSERT INTO `t2` VALUES (6,97,190,40);
SELECT t1.col_int_nokey, (select max(t2.col_int_key2) from t2 where t1.col_int_nokey) from t1;
drop table t1, t2;
CREATE TABLE `t1` (
`col_int_nokey` int(11) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (2);
INSERT INTO `t1` VALUES (0);
INSERT INTO `t1` VALUES (8);
INSERT INTO `t1` VALUES (5);
INSERT INTO `t1` VALUES (2);
INSERT INTO `t1` VALUES (33);
INSERT INTO `t1` VALUES (5);
INSERT INTO `t1` VALUES (1);
INSERT INTO `t1` VALUES (9);
INSERT INTO `t1` VALUES (1);
INSERT INTO `t1` VALUES (3);
INSERT INTO `t1` VALUES (8);
INSERT INTO `t1` VALUES (231);
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`col_int_key` int(11) NOT NULL,
`col_int_key2` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_int_key2` (`col_int_key2`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (3,6,1,1);
INSERT INTO `t2` VALUES (4,7,0,20);
INSERT INTO `t2` VALUES (5,0,1,30);
INSERT INTO `t2` VALUES (6,97,190,40);
SELECT t1.col_int_nokey, (select max(t2. col_int_ key2) from t2 where
t1.col_int_nokey) from t1;
drop table t1, t2;