Why Does My MySQL `NOT IN` Query Throw an 'Operand Should Contain 1 Column' Error?

MySQL NOT IN "Operand should contain 1 column" error in query

When using the NOT IN operator in a MySQL query, it is important to ensure that the right-hand operand (subquery) contains only one column. However, in some cases we may encounter "operand should contain 1 column" error, especially when using aggregate functions like COUNT() in a subquery.

Consider the following query:

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
)

This query aims to select all activities that are not in a subquery that counts the number of served entries for each activity and filters out those entries whose count is less than the frequency of the activity. However, MySQL throws an "operand should contain 1 column" error due to the presence of multiple columns in the subquery.

Solution:

To solve this problem, we need to modify the subquery to ensure that it only returns the id_campaign columns. The following is the modified version of the query:

SELECT *
FROM campaigns 
WHERE id NOT IN (
    SELECT id_campaign FROM (
        SELECT e.id_campaign AS id_campaign, d.frequency, 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 count(e.id) < d.frequency
    ) AS subquery
);

In this updated subquery, we only project the id_campaign columns, thus solving the error. The query now correctly excludes campaigns where the serviced entry count is less than the campaign frequency.

Or, for clearer code, you can rewrite it like this:

SELECT c.*
FROM campaigns c
WHERE c.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
);

This version selects NOT IN directly in the id_campaign clause, avoiding nested subqueries and making the code more concise and readable. The key is that the subquery on the right side of the NOT IN clause must return only a single column of data.

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