Select multiple columns in subquery

Question

Is there a way to select multiple columns in a subquery? I want to select two columns username and dateline from the posts table (3rd and 4th line), and I don't want to make two separate subqueries. Or is there a way to do this with a join?

SELECT `topics`.`id` AS `id`, `title`, `unique_views`, `tags`.`name` AS `name`, `bg_color`, `text_color`,
    `username`, `avatar`, `color`,
    (select `username` from `posts` where `topic_id` = `topics`.`id` order by `dateline` desc) as lastpost_username,
    (select `dateline` from `posts` where `topic_id` = `topics`.`id` order by `dateline` desc) as lastpost_dateline,
    (select `vote` from `topic_votes` where `topic_id` = `topics`.`id` and `voter_id` = :voter_id) as user_vote,
    (select count(*) from `topic_votes` where `topic_id` = `topics`.`id` and `vote` = 1) -
    (select count(*) from `topic_votes` where `topic_id` = `topics`.`id` and `vote` = -1) AS vote_sum
FROM `topics`
INNER JOIN `tags` ON `tags`.`id` = topics.`tag_id`
INNER JOIN `users` ON `topics`.`poster_id` = `users`.`id`
INNER JOIN `user_groups` ON `users`.`group_id` = `user_groups`.`id`
INNER JOIN `posts` ON `posts`.`topic_id` = `topics`.`id`
ORDER BY `topics`.`dateline` DESC
LIMIT 50

I'd start over, with proper CREATE and INSERT statements AND a desired result — Strawberry
– Strawberry, Commented Apr 24, 2016 at 8:04

madjardi · Accepted Answer · 2019-01-27 11:16:47Z

1

NOTE: for postgresql.
I no found similar question for postgresql. I hope this give any idea|help for both

You can use LATERAL for this purpose pseudocode

without lateral

SELECT *, T1.ID, S1.name from (
    select *, (select id from mysubquery) as TID from myTable
) AS T1
LEFT JOIN subquery S1 ON (T1.TID=S1.ID)  -- for take name

with lateral

select *, TID.id, TID.name  
    from myTable, 
    LATERAL (select id, name from mysubquery) as TID

edited Jan 27, 2019 at 11:16

answered Dec 29, 2018 at 8:45

madjardi

6,0192 gold badges39 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernd Buffen · Accepted Answer · 2016-04-24 09:44:56Z

You can do it like this. You can join a derived table, but i cant testet:

SELECT `topics`.`id` AS `id`, `title`, `unique_views`, `tags`.`name` AS `name`, `bg_color`, `text_color`,
    `username`, `avatar`, `color`,
    new_table.lastpost_username,
    new_table.lastpost_dateline,
    (select `vote` from `topic_votes` where `topic_id` = `topics`.`id` and `voter_id` = :voter_id) as user_vote,
    (select count(*) from `topic_votes` where `topic_id` = `topics`.`id` and `vote` = 1) -
    (select count(*) from `topic_votes` where `topic_id` = `topics`.`id` and `vote` = -1) AS vote_sum
FROM `topics
LEFT JOIN (
        select `username`,`dateline` FROM `posts`  ORDER BY `dateline` DESC
) AS new_table ON `new_table`.`topic_id` = `topics`.`id`
INNER JOIN `tags` ON `tags`.`id` = topics.`tag_id`
INNER JOIN `users` ON `topics`.`poster_id` = `users`.`id`
INNER JOIN `user_groups` ON `users`.`group_id` = `user_groups`.`id`
INNER JOIN `posts` ON `posts`.`topic_id` = `topics`.`id`
ORDER BY `topics`.`dateline` DESC
LIMIT 50

Collectives™ on Stack Overflow

Select multiple columns in subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related