CODEWITHSUNDEEP
mysqlsqlmysql-error-1241
reefine
reefine

Reputation: 853

Operand Should Contain 1 Column - MySQL NOT IN

SELECT * from campaigns WHERE id not in
(SELECT 
    e.id_campaign, d.name, d.frequency, d.country, d.referral, d.bid, d.status, COUNT(e.id) AS countcap  
    FROM campaigns d
    LEFT JOIN served e
    ON d.id = e.id_campaign 
    WHERE 
        d.status = 'Active'
    GROUP BY e.id_campaign
    HAVING
        countcap < d.frequency)

I get the error "Operand Should Contain 1 Column" - but I need the COUNT(e.id)

Upvotes: 4

Views: 5285

Answers (3)

Moshiur Rahman
Moshiur Rahman

Reputation: 1642

SELECT * from campaigns WHERE id not in
(SELECT 
e.id_campaign, d.name, d.frequency, d.country, d.referral, d.bid, d.status, 
COUNT(e.id) AS countcap  
FROM campaigns d....
)

You are checking id not in so ,you should check only one column for check id.

you can write this (It may not fulfill your purpose): 

SELECT * from campaigns WHERE id not in
(SELECT 
e.id_campaign from campaigns d
LEFT JOIN served e
ON d.id = e.id_campaign 
WHERE 
    d.status = 'Active'
GROUP BY e.id_campaign
HAVING
    countcap < d.frequency
)

Upvotes: 0

mu is too short
mu is too short

Reputation: 434665

There's always this:

select *
from campaigns 
where id not in (
    select id_campaign from (
        select e.id_campaign as id_campaign, d.frequency, e.id
        from campaigns d left join served e on d.id = e.id_campaign
        where d.status = 'Active'
        group by e.id_campaign 
        having count(e.id) < d.frequency 
    )
)

Upvotes: 5

Chandranshu
Chandranshu

Reputation: 3669

The 'not in' clause expects a list of values. So, a query such as the following will work:

SELECT * from campaigns WHERE id not in
(SELECT 
    e.id_campaign
    FROM campaigns d
    LEFT JOIN served e
    ON d.id = e.id_campaign 
    WHERE 
        d.status = 'Active'
    GROUP BY e.id_campaign
    HAVING
        COUNT(e.id) < d.frequency)

If you can tell us the structure of tables and what you want to select, we'll be able to figure out the right query for you.

Upvotes: 0

Related Questions