How to limit the result by categories on the query

Question

The following code shows me the posts on the carousel ordered by date and with exceptions for some categories.

But if someone posts in the same category it takes the full list. What I am trying to do is limit in two posts per category. I would appreciate any suggestions to make that work.

Current SQL:

SELECT 
  p.idPost,
  p.isAvailable,
  p.title,
  p.description,
  p.insertDate,
  c.idCategoryParent
FROM 
  posts p
  INNER JOIN categories c
    ON c.idCategory = p.idCategory
WHERE 
  (
    p.isAvailable = 1
    AND (c.idCategoryParent NOT IN (3)) 
  )
ORDER BY 
  p.insertDate DESC 
LIMIT 12

table : post

+--------+------------+-------------+---------------+---------------------+------------+
| idPost | idCategory | isAvailable |    title      |    description      | insertDate |
+--------+------------+-------------+---------------+---------------------+------------+
|   1    |      6     |     1       |   Red Ford    |  The Ford red car...| 2022-12-15 |
|   2    |      4     |     1       |   Bike cross  |  The bike cross...  | 2022-12-15 |
|   3    |      5     |     1       |   Black Honda |  The Black car...   | 2022-12-15 |
|   4    |      6     |     1       |   Blue Ford   |  The blue Ford      | 2022-12-15 |
|   5    |      6     |     1       |   Pink Ford   |  The pink Ford      | 2022-12-15 |
|   6    |      6     |     1       |   Green Ford  |  The Green Ford car | 2022-12-15 |
|   7    |      9     |     1       |   Airbus I    |  The Airbus jet I   | 2022-12-15 |
|   8    |      9     |     1       |   Aibus II    |  The Airbus jet II  | 2022-12-15 |
|   9    |      9     |     1       |   Airbus III  |  The Airbus jet III | 2022-12-15 |
+--------+------------+-------------+---------------+---------------------+------------+

table : categories

+------------+------------------+-----------------+
| idCategory | idCategoryParent |      name       |
+------------+------------------+-----------------+
|     1      |        0         |       Car       |
|     2      |        0         |       Plane     |
|     3      |        0         |       Bike      |
|     4      |        3         |    Bike Cross   |
|     5      |        1         |       Honda     |
|     6      |        1         |       Ford      |
|     7      |        1         |       Mercedes  |
|     8      |        2         |       Boeing    |
|     9      |        2         |       Airbus    |    
+------------+------------------+-----------------+

The current result:

+--------+------------+-------------+---------------+---------------------+------------+
| idPost | idCategory | isAvailable |    title      |    description      | insertDate |
+--------+------------+-------------+---------------+---------------------+------------+
|   1    |      6     |     1       |   Red Ford    |  The Ford red car...| 2022-12-15 |
|   3    |      5     |     1       |   Black Honda |  The Black car...   | 2022-12-15 |
|   4    |      6     |     1       |   Blue Ford   |  The blue Ford      | 2022-12-15 |
|   5    |      6     |     1       |   Pink Ford   |  The pink Ford      | 2022-12-15 |
|   6    |      6     |     1       |   Green Ford  |  The Green Ford car | 2022-12-15 |
|   7    |      9     |     1       |   Airbus I    |  The Airbus jet I   | 2022-12-15 |
|   8    |      9     |     1       |   Aibus II    |  The Airbus jet II  | 2022-12-15 |
|   9    |      9     |     1       |   Airbus III  |  The Airbus jet III | 2022-12-15 |
+--------+------------+-------------+---------------+---------------------+------------+

The result expected with 2 maximum posts per category:

+--------+------------+-------------+---------------+---------------------+------------+
| idPost | idCategory | isAvailable |    title      |    description      | insertDate |
+--------+------------+-------------+---------------+---------------------+------------+
|   1    |      6     |     1       |   Red Ford    |  The Ford red car...| 2022-12-15 |
|   3    |      5     |     1       |   Black Honda |  The Black car...   | 2022-12-15 |
|   4    |      6     |     1       |   Blue Ford   |  The blue Ford      | 2022-12-15 |
|   7    |      9     |     1       |   Airbus I    |  The Airbus jet I   | 2022-12-15 |
|   8    |      9     |     1       |   Aibus II    |  The Airbus jet II  | 2022-12-15 |
+--------+------------+-------------+---------------+---------------------+------------+

Answer 1

This is one way of getting the output using subquery. You could first limit the rows to two per category and then fetch the posts.

SELECT p.* FROM post p INNER JOIN categories c ON p.idCategory = c.idCategory 
WHERE p.isAvailable = 1 AND p.idPost IN (SELECT idPost FROM post 
WHERE idCategory = p.idCategory LIMIT 2) ORDER BY p.insertDate

I think in the expected output, you are supposed to get post with idCategory = 4 also as you have only one post with that category.