MySQL: Help with using join to simulate subquery

MicahCarrick · 09-29-2006, 12:07 PM

Hey y'all... I have this MySQL table...

Code:

CREATE TABLE `table1` (
  `uin` int(11) NOT NULL auto_increment,
  `name` varchar(50) not null,
  `email` varchar(100) not null,
  `body` blob,
  PRIMARY KEY  (`uin`)
);

which I need to create a new table from excluding older rows which have a duplicate 'body' field. I have done this in MySQL 5 using a subquery:

Code:

CREATE TABLE table1 AS (
SELECT * FROM table1 WHERE uin IN
(SELECT MAX(uin) FROM table1 GROUP BY body)
);

However, it's giving an error in older MySQL. Is there a tricky way of doing a join with the same table to achieve the same effect?

zeitounator · 09-30-2006, 07:17 AM

Just put the result of your subquery in a temporary table and use it as a join to populate the other one.

Code:

CREATE TEMPORARY TABLE singleuin as SELECT MAX(uin) as maxuin FROM table1 GROUP BY body;
CREATE TABLE table2 AS SELECT table1.* FROM table1 JOIN singleuin ON table1.uin = singleuin.maxuin;

Once your mysql session finishes the singleuin temporary table will disapear living only table 1 and table 2

On the other hand, you can do this in only one query

Code:

CREATE TABLE table2 AS SELECT MAX(uin), name, email, body FROM table1 GROUP BY body;

Meanwhile... my prefered method would be to create the empty table and populate it with an 'insert into... select' statement.

Code:

CREATE TABLE `table2` (
  `uin` int(11) NOT NULL auto_increment,
  `name` varchar(50) not null,
  `email` varchar(100) not null,
  `body` blob,
  PRIMARY KEY  (`uin`)
);
INSERT INTO table2 (uin, name, email, body) SELECT MAX(uin), name, email, body FROM table1 GROUP BY body;