Mysql one to many relationship

I have two tables:

TABLE items with IDItem field TABLE comments with IDcomment, IDItem, datePublished and comment fields.

If I want to list last 10 comments it is no problem, I just sort the 'comments' table. The problem is when I want to list last ten comments on the individual items, that means items are not duplicated.

Any best way to achieve this in regards to using indexes? If I order by 'comments' and group by IDItem I don't get the last comment out on each item as the group seems to order randomly :-(

I found solution to bring开发者_高级运维 'lastDate' to the 'items' table, so I can sort by the items and I will have the correct sort order, but when I join to the comments table I get 10 rows of the same item id if it had 10 comments :-(

How is the proper way to join one to many so I get only one item from the left table with one item on the right table?

I am not sure if I was very clear.

It sounds like you're trying to get the 10 items returned that have the most recent 10 comments, with one comment per item correct?

If so, try this:

SELECT * FROM Items I
JOIN
(SELECT TOP 10 * FROM Comments C2 WHERE DatePublished=
       (SELECT MAX(DatePublished) FROM Comments C3 WHERE C2.IDItem=C3.IDItem)
       ORDER BY DatePublished DESC) C1
ON I.IDItem=C1.IDItem

Edited: Removed extra SELECT and added limit of 10 comments returned

DECLARE @item TABLE
(
  IDItem int
)
DECLARE @comment TABLE
(
  IDComment int,
  DatePublished date,
  IDItem int,
  Comment varchar(100)
)

INSERT INTO @item (IDItem) VALUES (1);
INSERT INTO @item (IDItem) VALUES (2);
INSERT INTO @item (IDItem) VALUES (3);
INSERT INTO @item (IDItem) VALUES (4);

INSERT INTO @comment (IDComment, DatePublished, IDItem, Comment) VALUES (1,'2011-01-01', 1, 'test1');
INSERT INTO @comment (IDComment, DatePublished, IDItem, Comment) VALUES (2,'2011-01-02', 1, 'test2');
INSERT INTO @comment (IDComment, DatePublished, IDItem, Comment) VALUES (3,'2011-01-01', 2, 'test3');
INSERT INTO @comment (IDComment, DatePublished, IDItem, Comment) VALUES (4,'2011-01-03', 2, 'test4');
INSERT INTO @comment (IDComment, DatePublished, IDItem, Comment) VALUES (5,'2011-01-02', 3, 'test5');
INSERT INTO @comment (IDComment, DatePublished, IDItem, Comment) VALUES (6,'2011-01-05', 3, 'test6');

SELECT i.IDItem, (SELECT TOP 1 c.Comment FROM @comment c WHERE c.IDItem = i.IDItem ORDER BY c.DatePublished) FROM @item i

RETURNS

   1    test1
   2    test3
   3    test5
   4    NULL

If it's what are you looking for, just mysql this code. Replace TOP 1 with LIMIT 1 etc.

(Updated) What about this?

SELECT IDcomment, IDitem from COMMENTS where IDitem in (SELECT DISTINCT(IDitem) FROM comments);