16

As the title says, I wanted a workaround for this...

SELECT 
  comments.comment_id,
  comments.content_id,
  comments.user_id,
  comments.`comment`,
  comments.comment_time,
  NULL
FROM
  comments
WHERE
  (comments.content_id IN (SELECT content.content_id FROM content WHERE content.user_id = 1 LIMIT 0, 10))

Cheers

Atif
  • 10,395
  • 19
  • 62
  • 95

1 Answers1

18
SELECT  comments.comment_id,
        comments.content_id,
        comments.user_id,
        comments.`comment`,
        comments.comment_time,
        NULL
FROM    (
        SELECT  content.content_id
        FROM    content
        WHERE   content.user_id = 1
        LIMIT 10
        ) q
JOIN    comments
ON      comments.content_id = q.content_id

You probably will want to add an ORDER BY into the nested query.

Quassnoi
  • 398,504
  • 89
  • 603
  • 604
  • 1
    great thanks for this one.. Well another thing.. Is there a way to LIMIT comments to 2 per content_id ? – Atif May 18 '10 at 10:46
  • @atif089: yes, see this article: http://explainextended.com/2009/03/06/advanced-row-sampling/ – Quassnoi May 18 '10 at 10:57
  • thanks for the link.. wouldn't this be too heavy ? As im using this for a custom made social networking website – Atif May 18 '10 at 10:59
  • I mean its for a social networking website, and Im on a shared server. So would it be using much CPU ? – Atif May 18 '10 at 11:12
  • @atif: with a proper indexing, this would be very efficient. – Quassnoi May 18 '10 at 11:14
  • @Quassoni This is the simplest to understand among other answers on other posts to the same question asked several time over the past few years. – Sunny Dec 27 '15 at 19:53