jave.web jave.web - 5 months ago 8
MySQL Question

MySQL/MariaDB - LIMIT joined query result while not losing any primary ID

I know how to limit a query results with

LIMIT a,b
but what if I want to
LIMIT
query that was created with
JOIN
s which has multiple rows of one primary ID while keeping at least one row per ID?

Let me explain more...*

I have table of posts, then there is a table of categories.

One post, however, can be in more categories.

When I do query for posts I also want to query for their categories at the same time...

...the thing is I only need 1 category max. per 1 post result (I don't care which one)

Current (exemplary) results

ID | title | content | category_ID | category_name
1 | First Post | ....... | 2 | Boring Articles
1 | First Post | ....... | 5 | Interesting Articles
1 | First Post | ....... | 1 | Mojo Tips
2 | Second Post | ....... | 5 | Interesting Articles
3 | Third Post | ....... | 5 | Interesting Articles
3 | Third Post | ....... | 4 | Fitness
4 | Fourth Post | ....... | NULL | NULL


The desired (exemplary) results - note that at least one row per post ID was kept!

ID | title | content | category_ID | category_name
1 | First Post | ....... | 2 | Boring Articles
2 | Second Post | ....... | 5 | Interesting Articles
3 | Third Post | ....... | 5 | Interesting Articles
4 | Fourth Post | ....... | NULL | NULL


But if I would do just something like

SELECT * FROM posts
LEFT JOIN relationships USING(ID)
LEFT JOIN categories USING(category_ID)
LIMIT 0,4


This would happen (loosing needed data - not 1 row per 1 ID anymore...):

ID | title | content | category_ID | category_name
1 | First Post | ....... | 2 | Boring Articles
1 | First Post | ....... | 5 | Interesting Articles
1 | First Post | ....... | 1 | Mojo Tips
2 | Second Post | ....... | 5 | Interesting Articles


PS: I would like to know how to solve this in MySQL/MariaDB and if there is a difference in doing this when using one or another - then what is the difference please? :)

Answer

One method uses a subquery:

SELECT p.*,
       (SELECT c.category_name
        FROM relationships r JOIN
             categories c
             USING (category_ID)
       WHERE r.id = p.id
       LIMIT 1
      ) as category_name
FROM posts p
LIMIT 0, 4;

Note: You would normally have an ORDER BY when using LIMIT.

Another method uses GROUP BY:

SELECT p.*, c.*
FROM posts p LEFT JOIN
     relationships r
     USING (ID) LEFT JOIN
     categories c
     USING (category_ID)
GROUP BY p.id
LIMIT 0, 4;

I am not a fan of using GROUP BY like this, but it is a valid MySQL extension.