LIMIT in JOIN

[Amir Habibi]

Hi there,

I've got 2 simple tables : PARENT and CHILD

mysql > SELECT * FROM parent

id | name
--------------
1 | Atari
2 | Commodore

mysql > SELECT * FROM child

id | parent_id | name
-------------------------
1 | 1 | Atari 2600
2 | 1 | Atari 520 ST
3 | 1 | Atari 1024 ST
4 | 2 | Amiga
5 | 2 | Commodore 64
6 | 1 | Atari 520 STE

When I do this simple query :

mysql>SELECT * FROM parent, child WHERE child.parent_id = parent.id

I get all the rows from the child table. That's OK.

But what I want is to have only 2 records from the child table. Something like this :

id | name | id | parent_id | name
----------------------------------
1 | Atari | 1 | 1 | Atari 2600
1 | Atari | 2 | 1 | Atari 520 ST
2 | Commodore | 4 | 2 | Amiga
2 | Commodore | 5 | 2 | Commodore 64

Is it possible to join parent and child by returning ONLY 2 records from the child table ?

Thanks a lot.

CREATE TABLE `child` (`id` smallint(6) NOT NULL default '0', `parent_id` smallint(6) NOT NULL default '0', `name` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `child` (`id`, `parent_id`, `name`) VALUES (1, 1, 'Atari 2600'),(2, 1, 'Atari 520 ST'),(3, 1, 'Atari 1024 ST'),(4, 2, 'Amiga'),(5, 2, 'Commodore 64'),(6, 1, 'Atari 520 STE');

CREATE TABLE `parent` (`id` smallint(6) NOT NULL default '0',`name` varchar(15) NOT NULL default '',PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `parent` (`id`, `name`) VALUES (1, 'Atari'),(2, 'Commodore');