CODEWITHSUNDEEP
sql
user6669
user6669

Reputation: 155

How to limit the result of query with a join?

I have two tables in my database:

categories

id
category
message

messages

id
title
message

I am trying to retrieve two messages with their categories. Every message can have multiple categories. I've tried with the following query:

SELECT categories.category, messages.id, messages.title, messages.message
FROM categories
RIGHT JOIN messages
ON messages.id = category.message
ORDER BY messages.id DESC
LIMIT 2
OFFSET 0

The ouput for this is something like:

category   id   title        message
test-cat   1    Test title   This is the message body
category2  1    Test title   This is the message body

However this query results in only two rows (because the message which is retrieved has multiple categories). How can I limit on the number of messages rather than the number of categories? So that the result is like:

category   id   title        message
test-cat   1    Test title   This is the message body
category2  1    Test title   This is the message body
test-cat   2    Another msg  This is content
test-cat2  2    Another msg  This is content
something  2    Another msg  This is content

Upvotes: 2

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Put the limit in a subquery:

SELECT categories.category, m.id, m.title, m.message
FROM categories RIGHT JOIN
     (select *
      from messages
      ORDER BY messages.id DESC
      limit 2
     ) m
     ON m.id = categories.message
ORDER BY m.id DESC

Upvotes: 3

Related Questions