SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s). sql query

Question

I have two table that named as 'data' and 'regions'. I want to get regions with data related records as item of regions records.

regions

RegionId -- Name -- ParentId

data

DataId -- RegiondId -- Url

Sample result:

[
{Name:"a", items:[{...},{...},{...}, ...]},
{Name:"b", items:[{...},{...},{...}, ...]},
]

I used this query

SELECT *,(
    SELECT * 
    FROM data 
    WHERE data.RegionId=regions.RegionId
) AS items 
FROM regions 
WHERE regions.ParentId=1

But I got SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s).

Important: Region has parent relation between its records. So I when write this condition data.RegionId=regions.Id it must return that items has matches RegionIds of it's childs.

Answer 1

A subquery in the select list must return one column and 1 row (single value), but you have a select * there, hence the error message.

Your query should be written as a simple inner join instead of a subquery:

SELECT *
FROM regions 
INNER JOIN data ON data.RegionId=regions.RegionId
WHERE regions.ParentId=1

Answer 2

I got this error when trying to insert several rows at once. I collected the query parameters into an array and concatenated the place holder question marks. The error was given when I had something like

INSERT INTO table (...) VALUES ((...),(...),(...))

when what I should have had was

INSERT INTO table (...) VALUES (...),(...),(...)

Notice the extra parenthesis that caused the error.