CODEWITHSUNDEEP
sql
Ken
Ken

Reputation: 833

count() on where clause from a different table

I have a search function to search for mysql results depending on their inputs. Now I wanted to include on the where clause the count() of likes for the result from a different table.

Like this scenario. I want to search "Dog" where count(like_id) >= 1.

The Likes Table that I want to include is: TABLE_GLOBAL_LIKES.

The Structure of TABLE_GLOBAL_LIKES is: Fields: like_id, user_id, products_id, blog_id.

BOTH TABLE_GLOBAL_PRODUCTS and TABLE_GLOBAL_LIKES has a common field of products_id and blog_id to associate with.

This is my working query that I want the Likes table to included.

SELECT SQL_CALC_FOUND_ROWS p.*,
       CASE
           WHEN p.specials_new_products_price > 0.0000
                AND (p.expires_date > Now()
                     OR p.expires_date IS NULL
                     OR p.expires_date ='0000-00-00 00:00:00')
                AND p.status != 0 THEN p.specials_new_products_price
           ELSE p.products_price
       END price
FROM ".TABLE_GLOBAL_PRODUCTS." p
INNER JOIN ".TABLE_STORES." s ON s.blog_id = p.blog_id
WHERE MATCH (p.products_name,
             p.products_description) AGAINST ('*".$search_key."*')
  AND p.display_product = '1'
  AND p.products_status = '1' HAVING price <= ".$priceto_key."
  AND price >= ".$pricefrom_key."
ORDER BY p.products_date_added DESC, p.products_name

I'm a newbie in mysql queries.. Please help.

Upvotes: 1

Views: 109

Answers (1)

Sych
Sych

Reputation: 1823

Try this:

SELECT SQL_CALC_FOUND_ROWS p.*, COUNT(l.like_id)
       CASE
           WHEN p.specials_new_products_price > 0.0000
                AND (p.expires_date > Now()
                     OR p.expires_date IS NULL
                     OR p.expires_date ='0000-00-00 00:00:00')
                AND p.status != 0 THEN p.specials_new_products_price
           ELSE p.products_price
       END price
FROM ".TABLE_GLOBAL_PRODUCTS." p
INNER JOIN ".TABLE_STORES." s ON s.blog_id = p.blog_id
INNER JOIN ".TABLE_GLOBAL_LIKES." l ON l.blog_id = p.blog_id AND l.products_id = p.products_id
WHERE MATCH (p.products_name,
             p.products_description) AGAINST ('*".$search_key."*')
  AND p.display_product = '1'
  AND p.products_status = '1' HAVING price <= ".$priceto_key."
  AND price >= ".$pricefrom_key."
GROUP BY p.products_id
HAVING COUNT(l.like_id)>0
ORDER BY p.products_date_added DESC, p.products_name

Upvotes: 1

Related Questions