MySQL Forums
Forum List  »  Newbie

Limit join result
Posted by: daniel n
Date: November 26, 2010 02:48PM

Hi there,

I can't find a solution on how to limit the result of a join to 2
I would appreciate any help.

I've got the following two tables. One is used for storing comments on products, the other stores the replies to the comments. product_comments_id and prodcom_id are the link between those tables


CREATE TABLE `product_comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) DEFAULT NULL,
`comment_user_id` int(11) DEFAULT NULL,
`comment` text,
`com_timestamp` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

and this one
CREATE TABLE `product_comments_reply` (
`prod_reply_id` int(11) NOT NULL AUTO_INCREMENT,
`prodcom_id` int(11) DEFAULT NULL,
`reply_user_id` int(11) DEFAULT NULL,
`reply_text` text,
`reply_timestamp` int(11) DEFAULT NULL,
PRIMARY KEY (`prod_reply_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

What I'd like to achieve is only display two replies for each comment. The following query displays all the replies for a comment.

SELECT * FROM product_comments, (SELECT * FROM product_comments_reply) as reply where product_comments.id=reply.prodcom_id order by prodcom_id;

I don't see how I can add a Limit 2 on the results of the product_comments_reply result



Edited 1 time(s). Last edit at 11/26/2010 02:49PM by daniel n.

Options: ReplyQuote


Subject
Written By
Posted
Limit join result
November 26, 2010 02:48PM
November 27, 2010 07:59AM
November 27, 2010 08:07AM


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.