MySQL - Operand should contain 1 column(s)

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 that this query would work no problem. Selecting columns, then selecting two more from another table, and continuing on from there. I just can't figure out what the problem is.

Is there a simple fix to this, or another way to write my query?

Answers


Your subquery is selecting two columns, while you are using it to project one column (as part of the outer SELECT clause). You can only 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

Need Your Help

Tokenizing a string into a list of nested arrays with Python

python recursion brainfuck

Following this document I'm writing an interpreter for Brainfuck, which in my implementation entails turning a string such as:

Python2 urllib/urllib2 wrong URL issue

python urllib2 urllib

I am coding a Python2 script to perform some automatic actions in a website. I'm using urllib/urllib2 to accomplish this task. It involves GET and POST requests, custom headers, etc.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.