Check if records are more than one for a record in joined table

Question

I have two tables: business and sms_content_business And this is the schema for both tables: For business Table:

For sms_content_business Table:

Now what I want to achieve is to get all the businesses for which is_topbrand = 1 there is at least one record in the sms_content_business table and I am doing it like this:

SELECT * FROM `business` WHERE is_topbrand=1 AND (SELECT COUNT(*) FROM `sms_content_business` scb JOIN `business` b ON b.id=scb.business_id) > 0

But it doesn't do what I intend to do. Also how to do it using Yii 1 CDBcriteria class? Any help?

Answer 1

For mysql query, you can use exists;

select b.*
from business b
where b.is_topbrand = 1
and exists(
    select 1
    from sms_content_business scb
    where b.id = scb.business_id
)