CODEWITHSUNDEEP
mysql
user813720
user813720

Reputation: 451

mysql count comparison statement

Is there a more efficient way to do this without counting twice?

i.e returning a warning image string if the count is 0 otherwise return the count.

IF(
  ((SELECT COUNT(pc.category_id) from product_categories pc WHERE pc.product_id = cp.ItemID) > 0),
  (SELECT COUNT(pc.category_id) from product_categories pc WHERE pc.product_id = cp.ItemID),
  "<img src=\"_imgs/warning.png\" alt=\"warning\"/>"
) AS category_count

Upvotes: 0

Views: 329

Answers (4)

Devart
Devart

Reputation: 121922

You also may join this table, e.g. -

SELECT
  cp.*,
  IF(pc.cnt > 0, pc.cnt, '<img src=\"_imgs/warning.png\" alt=\"warning\"/>')
FROM
  cp_table cp
  LEFT JOIN (
    SELECT product_id, COUNT(category_id) AS cnt FROM product_categories GROUP BY product_id
  ) pc
    ON pc.product_id = cp.ItemID

Upvotes: 0

user1076371
user1076371

Reputation: 161

Simple would be use COALESCE. NULLIF returns null when two arguments are same and COALESCE helps us return a default value when NULL is encountered.

select COALESCE(NULLIF(COUNT(pc.category_id),0),"<img src=\"_imgs/warning.png\" alt=\"warning\"/>") AS category_count

Upvotes: 0

a&#39;r
a&#39;r

Reputation: 36999

You can use a combination of NULLIF and COALESCE, eg.

COALESCE( NULLIF((sub-query), 0), 'other value')

However, you should not be constructing HTML in your database queries. This then means that you don't require any complicated SQL and this becomes a simple if statement in your view.

Upvotes: 0

lqez
lqez

Reputation: 3008

Use variable for it. (But I think its result may be cached by query cache.)

IF( @val:=(query here), @val, "other value" ) AS category_count

Upvotes: 2

Related Questions