CODEWITHSUNDEEP
mysqlsql
user10264314
user10264314

Reputation:

How to change conditional if select result is less than 10

Is it possible with MySQL to change conditional statements if the select result isn't reaching the limit?

the code below will select the main category only, their cp.level is 0 but if they are only 3 category how can I change cp.level to 1 until reaches the limit

SELECT DISTINCT
        name, cd.category_id
        FROM oc_category_description cd
        LEFT JOIN oc_category_path cp ON (cd.category_id = cp.path_id)
        WHERE name LIKE 'с%'
        AND cp.level = 0 LIMIT 10

Upvotes: 0

Views: 73

Answers (1)

Tom Dee
Tom Dee

Reputation: 2674

You can just filter the results of the query so cp.level is either 0 or 1, order by cp.level and keep the limit as 10. Like this:

SELECT DISTINCT
        name, cd.category_id
        FROM oc_category_description cd
        LEFT JOIN oc_category_path cp ON (cd.category_id = cp.path_id)
        WHERE name LIKE 'с%'
        AND cp.level in (0, 1) ORDER BY cp.level ASC
        LIMIT 10

However if you're unsure it is going to reach the level of 1 and want to show level 2 as well, you can just remove cp.level = 0 and order it by ORDER BY cp.level ASC instead.

If you want change the value you're filtering for on name based on the count, like you mentioned in your comment then you can do this:

WHERE (((subquery to get count) > 10 AND name LIKE 'с%') OR ((subquery to get count) < 10 AND name LIKE '%с'))

Upvotes: 0

Related Questions