Return 2 columns from MySQL subquery?

Question

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?

Answer 1

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

Answer 2

Another way to rewrite the query, without having 2 correlated inline subqueries is to have instead one LEFT JOIN in the FROM clause. The problem is that MySQL does not have LATERAL joins so it looks a bit clumsy and is quite confusing to get it right:

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

Answer 3

You may be able to get what you need from something like

SELECT x.username, x.avatarurl,
       ...
FROM ...
JOIN ( SELECT username, avatarurl
           FROM   users
     ) AS x  ON  id = topicanswers.userid