mysql multiple fields from subquery

Question

SELECT 
  (SELECT date FROM forums WHERE topic_id=f.id OR id=f.id ORDER BY id DESC LIMIT 1) as last_reply, 
  f.*, p.id as pid, p.name FROM forums f 
      INNER JOIN players p ON p.id = f.author 
  WHERE f.topic_id=0 ORDER BY f.id DESC

In the subquery, I'd like to return not only the date field, but also the author field as well. how can I do this?

looked at a similar post but can't apply it to mine.

Did you try adding author to the list of things you select, like this: (SELECT date, author FROM forums WHERE...) — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Apr 8, 2012 at 22:31
yes but the subquery is designed to search for the last posters id, not the topic poster id — user1022585
– user1022585, Commented Apr 8, 2012 at 22:39
I'd recommend you provide some info about the tables, because that query just doesn't seem right. — Mosty Mostacho
– Mosty Mostacho, Commented Apr 8, 2012 at 22:41

Arion · Accepted Answer · 2012-04-08 22:35:48Z

2

I would do it something like this:

SELECT 
  (SELECT date FROM forums WHERE topic_id=f.id OR id=f.id ORDER BY id DESC LIMIT 1) as last_reply,
  (SELECT author FROM forums WHERE topic_id=f.id OR id=f.id ORDER BY id DESC LIMIT 1) as last_author,
  f.*, p.id as pid, p.name FROM forums f 
      INNER JOIN players p ON p.id = f.author 
  WHERE f.topic_id=0 ORDER BY f.id DESC

I would actually repeat the subquery again

answered Apr 8, 2012 at 22:35

Arion

31.3k10 gold badges74 silver badges89 bronze badges

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

Comments

BGuimberteau · Accepted Answer · 2012-04-08 22:39:33Z

0

Maybe something like this can help you :

SELECT MAX(f.date), f.author
FROM forums f
    INNER JOIN players p ON
        p.id = f.author
WHERE f.tpoic_id = 0
GROUP BY f.author
ORDER BY f.id DESC

But it's difficult with no structure of tables.

Good luck.

answered Apr 8, 2012 at 22:39

BGuimberteau

2491 silver badge8 bronze badges

Collectives™ on Stack Overflow

mysql multiple fields from 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