2

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 |
+--------+------------+-------------+---------------+---------------------+------------+
Adriano
  • 31
  • 2
  • 1
    Please correctly tag your DBMS, as per the [tag:sql] tag description. Your previous tags appeared to be irrelevant and/or erroneous. This seems to be primarily a SQL question. Also please provide sample data and expected result - we cannot predict what the code will do if we can't see the data it's working with, and your description of what's expected is also very vague. See also [ask] and [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) to improve your post. Thanks. – ADyson Dec 21 '22 at 11:35
  • Thanks for the update. It's improved the question. But you do still need to tag the DBMS (e.g. MySQL, MS SQL Server, Oracle, PostgreSQL or whatever) - SQL syntax and available functions vary across different SQL products – ADyson Dec 21 '22 at 13:49

1 Answers1

0

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.

Prasuna
  • 49
  • 4
  • Thanks for your help. But not working with Limit on the subquery. There is a way to use Limit on the subquery and I am researching how. Your suggest was important to give the start point. – Adriano Dec 22 '22 at 10:52