MySQL Forums
Forum List  »  General

LIMIT in JOIN
Posted by: Amir Habibi
Date: January 16, 2007 03:26AM

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');

Options: ReplyQuote


Subject
Written By
Posted
LIMIT in JOIN
January 16, 2007 03:26AM
January 16, 2007 03:30AM
January 16, 2007 03:43AM
January 16, 2007 03:51AM
January 16, 2007 05:48AM
January 16, 2007 06:29AM
January 16, 2007 06:53AM
January 16, 2007 06:59AM
January 16, 2007 07:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.