Maik Hagenbruch Maik Hagenbruch - 1 year ago 90
SQL Question

mysql subquery select with field from query

i have a little problem with a subquery in sql.
her the query

SELECT st.title, count( as question_count, max( as maxid,
sum(case when a.answer is not null then 1 else 0 end) as answer_count, g.user_id as game_user_id,
a.game_id as a_game_id, a.modified as finished, (select modified as finished from answers a where = g.maxid limit 1) as subquery
FROM games g
left join answers a on(a.game_id =
left join questions q on(a.question_id =
left join sessions s on( = q.session_id)
left join sessiontypes st on( = s.sessiontype_id)
WHERE g.user_id = 21
group by
having(question_count = answer_count)
order by finished DESC;

i want that the subquery returns the modified value from answers where the id is the highest grouped by game.

so i tried to
select max(id) as maxid...
and the use max id in the subquery.
where = maxid
. nice try, but dont work.
mysql error is this one:
Reference 'maxid' not supported (reference to group function)

can anybody give a hint how to solve that?

Answer Source

Join on a subquery which returns the MAX( grouped by the answers.game_id.

Then use that maxid to join on the answers table to get the row of the corresponding

Not sure, how your result is supposed to look, in your select I removed a.modified AS finished, and replaced it with the modified column of the row with maxid.

    count( AS question_count,
        WHEN a.answer IS NOT NULL THEN
    ) AS answer_count,
    g.user_id AS game_user_id,
    a.game_id AS a_game_id,
    modifiedAnswer.modified AS finished,
    games g

LEFT JOIN (SELECT MAX( AS maxid, game_id FROM answers GROUP BY answers.game_id) AS a ON (a.game_id =
LEFT JOIN answers AS modifiedAnswer ON = a.maxid 

LEFT JOIN questions q ON (a.question_id =
LEFT JOIN sessions s ON ( = q.session_id)
LEFT JOIN sessiontypes st ON ( = s.sessiontype_id)
    g.user_id = 21
        question_count = answer_count
    finished DESC;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download