MaximeBernard MaximeBernard - 6 months ago 11
SQL Question

Why I can't select more than one column in my subquery?

I've been doing this query

SELECT
A.*,
(
SELECT
SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count,
SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_video_count
FROM B
WHERE B.A_id = A.id
)
FROM A


which answers me with a
Operand should contain 1 column(s)
whereas

SELECT
A.*,
(
SELECT
SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count
FROM B
WHERE B.A_id = A.id
)
FROM A


works perfectly. Why MariaDB would not let me subquery more than 1 column ? Am I missing something?




As this question (MySQL - Operand should contain 1 column(s)) suggests,

You probably would answer that I should go this way

SELECT t1.*, sq.*
FROM table1 t1,
(SELECT a,b,c FROM table2 ...) sq
WHERE ...


or using a join (but my query is much more complicated and my many SUMS and COUNTS mess a lot with my GROUP BY).

My question is more about "Why" than "How".




My final solution (which is not optimal):

SELECT
A.*,
(
SELECT
SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count,
FROM B
WHERE B.A_id = A.id
)
(
SELECT
SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_video_count
FROM B
WHERE B.A_id = A.id
)
FROM A

Answer

IMO No Database should let you select two columns in ( ) like that. It's clearly that you can not do select col1, (col2_ok), (multi_columns_HOW_ON_EARTH?)

One solution should be using tmp table like this (searched but MariaDB not support cte):

select A.*, tmp.A_picture_count, tmp.A_video_count
from A
inner join (SELECT A.id,
                SUM(IF(LENGTH(B.picture) > 0, 1, 0)) AS A_picture_count,
                SUM(IF(LENGTH(B.video) > 0, 1, 0)) AS A_video_count
            FROM A
            inner join B
            on B.A_id = A.id
            group by A.id
            ) tmp
on A.id = tmp.id;