How to limit results from a JOIN and not from the result?

Indeed, if you use the LIMIT 3at the end of the query, you will be limited in the number of categories.

To reduce the news by category, the LIMIT 3must go in the GROUP_CONCATsuch that like this:

GROUP_CONCAT(
  COALESCE(n.mini_title,''),'≠',
  COALESCE(n.url,''
  SEPARATOR '|'
  LIMIT 3
)

Following @OscarGarcia's comment, if your MySQL server does not support LIMITen GROUP_CONCAT, you can try substituting the line:

foreach ($arrTitleNews as $theData){

By:

foreach ($arrTitleNews as $i=>$theData) if($i<3){

Which basically solves the problem from PHP, assigning a number to each news and keeping the top 3.

To solve your problem, you could use an LIMITinside GROUP_CONCAT()as @David JP suggests , but it would only work if you use a version of MariaDB 10.3.3 or higher .

To make it work in any version of MySQL you should use this little trick with user variables that I propose:

SELECT
  title_category,
  GROUP_CONCAT(
    COALESCE(mini_title,''),
    CONVERT('≠' USING utf8),
    COALESCE(url,'')
    SEPARATOR '|'
  ) data
FROM (
  SELECT
    IF(@IDC = c.id_category, @NUM := @NUM + 1, @NUM := 1) num,
    @IDC := c.id_category id_category,
    c.title_category,
    n.id_news,
    n.mini_title,
    n.url
  FROM category c
  CROSS JOIN (SELECT @IDC := 0, @NUM := 0) iniciar
  INNER JOIN news n
    ON c.id_category = n.id_category
  WHERE
    language = 'es'
    AND c.active = 'S'
    AND n.active
  ) subconsulta
WHERE num <= 3
GROUP BY title_category;

What are you doing?

To start with I initialize the user variables to 0con CROSS JOIN (SELECT @IDC := 0, @NUM := 0) iniciar(to avoid a SET @IDC := 0, @NUM := 0previous). Otherwise, the initial value would be null, causing unwanted behavior.

To continue, for each row I perform the following check:

• If the value of @IDCdoes not match id_category(we have started a new category) start @NUMa 1.
• If the value matches then I increment the value of @NUM.

After that I update the value of @IDCto the value of id_category.

This has the effect of counting the elements within each category, so at the end of the query I can filter all the records that have a count equal to or less than 3.

You can see the operation online at the following address:

• https://www.db-fiddle.com/f/wYin7BRpMnTLKST1vfv7oj/4

Although I would suggest changing your SQL and PHP code so that the following works:

SELECT
  id_category,
  title_category,
  id_news,
  mini_title,
  url
FROM (
  SELECT
    IF(@IDC = c.id_category, @NUM := @NUM + 1, @NUM := 1) num,
    @IDC := c.id_category id_category,
    c.title_category,
    n.id_news,
    n.mini_title,
    n.url
  FROM category c
  CROSS JOIN (SELECT @IDC := 0, @NUM := 0) iniciar
  INNER JOIN news n
    ON c.id_category = n.id_category
  WHERE
    language = 'es'
    AND c.active = 'S'
    AND n.active
  ) subconsulta
WHERE num <= 3

You can see the operation online at the following address:

• https://www.db-fiddle.com/f/wYin7BRpMnTLKST1vfv7oj/1

This SQL code will require changes to your PHP code to get the data, as each record is not a category.

Finally, I have to warn you that you should not use GROUP_CONCAT()to get aggregated data obtained from other tables. You could run into problems if they contain the characters you use as a delimiter.

Instead you should use, for example, JSON_ARRAYAGG(). It allows you to JSON format the aggregated data in a way that misinterpretation is not possible.

For example:

SELECT
  c.title_category,
  JSON_ARRAYAGG(JSON_OBJECT('title', n.mini_title, 'url', n.url)) data
FROM category c
INNER JOIN news n
  ON c.id_category=n.id_category
WHERE
  language = 'es'
  AND c.active = 'S'
  AND n.active
GROUP BY c.id_category, c.title_category

See online example:

• https://www.db-fiddle.com/f/wYin7BRpMnTLKST1vfv7oj/3