MySQL Subquery – Return Two Columns

join;MySQLsubquery

I'm new in MySQL programing and I have one problem.
I created this query:

SELECT *,
       topics.createdate AS TopicCreateDate,
       (SELECT (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost
FROM   topics
       INNER JOIN categories
               ON topics.categoryid = categories.id
       INNER JOIN users
               ON topics.userid = users.id
ORDER  BY topics.lastupdate DESC

This query lists all Topics, joins categories and users by UserID and CategoryID in Topics and Username from TopicAnswers as LastPost. It orders them descending by LastUpdate in Topics that controlled by TRIGER.

It work pretty well, but there is a problem with this subquery:

            (SELECT (SELECT username
                    FROM   users
                    WHERE  id = topicanswers.userid)
            FROM   topicanswers
            WHERE  topicid = topics.id
            ORDER  BY id DESC
            LIMIT  1)        AS LastPost

It selects Username, of last post in topic, from Users by UserID from TopicAnswers as LastPost, now I want to select another one column from table Users, AvatarURL, but when I add :

                        (SELECT username, avatarurl
                        FROM   users
                        WHERE  id = topicanswers.userid)

It returns error:

1241 Operand should contain 1 column(s)

How I can slove this problem?

Best Answer

it is absolutely correct error, You are select single column in main select, so it must return only one column for avoid collisions

for each column make a new sub-select:

                (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid) as username

                (SELECT avatarurl
                FROM   users
                WHERE  id = topicanswers.userid) as avatarur1

the same will be relate and to TOP level select:

   (SELECT (SELECT username
            FROM   users
            WHERE  id = topicanswers.userid)

IF ADD SECOND QUERY THERE - it will throw an error for TOP Level SELECT

FROM   topicanswers
WHERE  topicid = topics.id
ORDER  BY id DESC
LIMIT  1)        AS LastPost

You query should be like this :

SELECT *,
       topics.createdate AS TopicCreateDate,
       (SELECT (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost_name,

       (SELECT (SELECT avatarur1
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost_avatarur1

    FROM   topics
           INNER JOIN categories
                   ON topics.categoryid = categories.id
           INNER JOIN users
                   ON topics.userid = users.id
    ORDER  BY topics.lastupdate DESC
Related Question