Get only 3 rows from joined table

Question

I'm trying to get only three rows from the joined table. For example, I have a categories table. I want to get popular categories and 3 articles from these categories without using SQL inside loop. Total post count should be 12.

I tried this but didn't work. (If I set limit 1 it works. But not working on limit 3)

SELECT 
    categories.name AS cname,
    categories.url  AS curl,
    articles.name,
    articles.url,
    articles.image
FROM   
    categories
    JOIN articles ON articles.id = (
        SELECT p.id
        FROM articles AS p
        WHERE categories.id = p.category AND p.delete = '0'
        ORDER  BY p.id DESC
        LIMIT  3
    )
WHERE  
    categories.delete = '0'
    AND categories.popular = '1'

replace your syntax **JOIN articles ON articles.id = (** with **JOIN articles ON articles.id IN (** — Primit, Sep 02 '19 at 11:24
Possible duplicate of [Alternative to using LIMIT keyword in a SubQuery in MYSQL](https://stackoverflow.com/questions/12810346/alternative-to-using-limit-keyword-in-a-subquery-in-mysql) — mickmackusa, Sep 02 '19 at 20:07

score 2 · Answer 1 · answered Sep 02 '19 at 11:22

In MySQL 8+, you would simply do:

SELECT c.name AS cname, c.url  AS curl,
       a.name, a.url, a.image
FROM categories c JOIN
     (SELECT a.*,
             ROW_NUMBER() OVER (PARTITION BY a.category ORDER BY a.id DESC) as seqnum
      FROM articles a
      WHERE a.delete = 0
     ) a
     ON a.category = c.id
WHERE a.seqnum <= 3 AND
      c.delete = 0 AND
      c.popular = 1

score 1 · Answer 2 · answered Sep 02 '19 at 10:45

1

Your subquery returns multiple records (actually 3), so the equality operator should be replaced with the IN operator.

Consider:

SELECT 
    categories.name AS cname,
    categories.url  AS curl,
    articles.name,
    articles.url,
    articles.image
FROM   
    categories
    JOIN articles ON articles.id IN (
        SELECT p.id
        FROM articles AS p
        WHERE categories.id = p.category AND p.delete = '0'
        ORDER  BY p.id DESC
        LIMIT  3
    )
WHERE  
    categories.delete = '0'
    AND categories.popular = '1'

answered Sep 02 '19 at 10:45

GMB

216,147
25
84
135

i get this error in phpmyadmin: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Sep 02 '19 at 13:47
@Cihan: which version of MySQL are you using? – GMB Sep 02 '19 at 15:48

Get only 3 rows from joined table

2 Answers2