#1241: Operand should contain 1 column(s)

Question

I get the error code '#1241: Operand should contain 1 column(s)'. The code worked once, I didn't alter it though. But now it doesn't work anymore.

What is the problem? It's something with the IN operator.

The code as below:

SELECT DISTINCT 
    composition.compartikelnr, 
    composition.artikelnr,
    aantal 
FROM 
    webshops.composition 
WHERE 
    composition.compartikelnr IN (
        SELECT 
            producten.artikelnr, 
            producten.merk, 
            producten.producttype,
            producten.naam, 
            producten.hoeveelheid, 
            producten.eenheid, 
            producten.kleur, 
            prodprice.eurotoner, 
            producten.ean, 
            producten.seourl, 
            image.image, 
            producten.staat, 
            producten.huismerk, 
            producten.shopping 
        FROM 
            webshops.producten 
            LEFT JOIN 
            webshops.prodprice 
            ON producten.artikelnr = prodprice.artikelnr
            LEFT JOIN (SELECT * FROM webshops.prodimage WHERE priority = 1) image ON producten.artikelnr = image.artikelnr 
        WHERE 
            image.image <> '' AND 
            producten.active = 1 AND 
            producten.ean <> '' AND 
            producten.shopping = 1
    ) AND 
    composition.artikelnr IN (
        SELECT 
            producten.artikelnr, 
            producten.merk, 
            producten.producttype, 
            producten.naam, 
            producten.hoeveelheid, 
            producten.eenheid, 
            producten.kleur, 
            prodprice.eurotoner, 
            producten.ean, 
            producten.seourl, 
            image.image, 
            producten.staat, 
            producten.huismerk, 
            producten.shopping 
        FROM 
            webshops.producten 
            LEFT JOIN webshops.prodprice ON producten.artikelnr = prodprice.artikelnr 
            LEFT JOIN (SELECT * FROM webshops.prodimage WHERE priority = 1) image ON producten.artikelnr = image.artikelnr 
        WHERE 
            image.image <> '' AND 
            producten.active = 1 AND 
            producten.ean <> '' AND 
            producten.shopping = 1
    )

I've reformatted your SQL; try not to write SQLs all on one line - it makes them hard to read, understand and reduces the likelihood that other people will want to help you — Caius Jard, Nov 13 '18 at 13:27

Caius Jard · Answer 1 · 2018-11-13T13:31:20.217

You have written IN( SELECT more,than,one,column,here FROM..)

IN requires you to select exactly one column from the subquery, so choose whichever column is related to what you're INning.

Perhaps this:

SELECT DISTINCT 
    composition.compartikelnr, 
    composition.artikelnr,
    aantal 
FROM 
    webshops.composition 
WHERE 
    composition.compartikelnr IN (
        SELECT 
            producten.artikelnr 
        FROM 
            webshops.producten 
            LEFT JOIN 
            webshops.prodprice 
            ON producten.artikelnr = prodprice.artikelnr
            LEFT JOIN (SELECT * FROM webshops.prodimage WHERE priority = 1) image ON producten.artikelnr = image.artikelnr 
        WHERE 
            image.image <> '' AND 
            producten.active = 1 AND 
            producten.ean <> '' AND 
            producten.shopping = 1
    ) AND 
    composition.artikelnr IN (
        SELECT 
            producten.artikelnr
        FROM 
            webshops.producten 
            LEFT JOIN webshops.prodprice ON producten.artikelnr = prodprice.artikelnr 
            LEFT JOIN (SELECT * FROM webshops.prodimage WHERE priority = 1) image ON producten.artikelnr = image.artikelnr 
        WHERE 
            image.image <> '' AND 
            producten.active = 1 AND 
            producten.ean <> '' AND 
            producten.shopping = 1
    )

But I know nothing about your data structure, I just found columns that seemed to be related to artikelnr based on the name. I make no warranties that this code I've posted works, simply that it demonstrates what I mean by "select only one column in the SELECT query that is inside the IN brackets"

The code worked once, I didn't alter it though. But now it doesn't work anymore.

That is incredibly unlikely, I'm afraid.. At best the only explanation I have for that is that MySQL has some magical feature setting that allows you to select more than one column within an IN and it knows what to pick (or it blindly chooses the first column) and someone's altered the setting. If it does have such a setting, changing it to behave like other databases would be a good thing

#1241: Operand should contain 1 column(s)

1 Answers1