While working on a system i'm creating i attempted to use the following query in my project

SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by,
    users.id AS posted_by_id
    FROM users
    WHERE users.id = posts.posted_by)
FROM topics
LEFT OUTER JOIN posts ON posts.topic_id = topics.id
WHERE topics.cat_id = :cat
GROUP BY topics.id

":cat" is bound by my PHP code as I'm using PDO. 2 is a valid value for ":cat".

That query though gives me an error: "#1241 - Operand should contain 1 column(s)"

What stumps me is that i would think this question would be perfect Selecting columns, then selecting two more from another table, and continuing on from there. I just don't know what the problem is

Is there any way to write a query?

Best Answer


Your subquery is selecting two columns, while you are using it to project one column (as part of the outer SELECT clause). You can select one column from such a query in this context

Consider joining to the users table instead; this will give you more flexibility when selecting what columns you want from users .

SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
users.username AS posted_by,
users.id AS posted_by_id

FROM topics

LEFT OUTER JOIN posts ON posts.topic_id = topics.id
LEFT OUTER JOIN users ON users.id = posts.posted_by

WHERE topics.cat_id = :cat
GROUP BY topics.id