MySQL NOT IN Error: Why 'Operand Should Contain 1 Column'?

MySQL NOT IN query failed: operand should contain 1 column

When you encounter the "operand should contain 1 column" error in a MySQL NOT IN query, it's crucial to understand the root cause.

This error occurs because the operand of the NOT IN condition contains multiple columns:

<code class="language-sql">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  </code>

The NOT IN operator requires its operand to be a single column, while the subquery returns rows containing multiple columns.

Solution:

One way to solve this error is to extract the necessary columns from the subquery and compare it with the id column of the outer query:

<code class="language-sql">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 
    ) as subquery
)</code>

Ensures correct use of the id_campaign operator by limiting the subquery results to a single column NOT IN. Additionally, we added an alias as subquery for the subquery, which is necessary in some MySQL versions. This improves query clarity and readability.

The above is the detailed content of MySQL NOT IN Error: Why 'Operand Should Contain 1 Column'?. For more information, please follow other related articles on the PHP Chinese website!