Geek asks and answers

The operand must 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 "The operand must contain 1 column" - but I need COUNT (e.id)

+3
source share
2 answers

It is always:

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 
    )
)
+4
source

The not-in clause expects a list of values. Thus, the following query 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 about the structure of the tables and what you want to choose, we can find the right query for you.

0
source

More articles:


All Articles