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,623
  • 20
  • 63
  • 96

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
  • 413,100
  • 91
  • 616
  • 614
  • 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