How to Fix the 'Operand Should Contain 1 Column' Error in MySQL's NOT IN Subquery?

MySQL "Operand Should Contain 1 Column" Error in NOT IN Subqueries: A Solution

MySQL's NOT IN clause requires the subquery to return a single column. The following query generates the "Operand should contain 1 column" error because the subquery returns multiple columns:

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)

The problem lies in selecting multiple fields (e.g., e.id_campaign, d.name, d.frequency, etc.) within the subquery. NOT IN expects a single column for comparison.

The Solution: Using a Derived Table

To correct this, we can employ a derived table (a subquery in the FROM clause) to isolate the relevant column:

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);

This revised query uses a derived table named subquery. This inner query performs the original logic, but only selects e.id_campaign (aliased as id_campaign), ensuring the outer NOT IN clause receives a single-column result set, thus resolving the error. The original filtering logic based on frequency and countcap is retained within the derived table.

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