Maik Hagenbruch Maik Hagenbruch - 4 months ago 25
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(q.id) as question_count, max(a.id) 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 a.id = g.maxid limit 1) as subquery
FROM games g
left join answers a on(a.game_id = g.id)
left join questions q on(a.question_id = q.id)
left join sessions s on(s.id = q.session_id)
left join sessiontypes st on(st.id = s.sessiontype_id)
WHERE g.user_id = 21
group by g.id
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 a.id = 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

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

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

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.

SELECT
    st.title,
    count(q.id) AS question_count,
    sum(
        CASE
        WHEN a.answer IS NOT NULL THEN
            1
        ELSE
            0
        END
    ) AS answer_count,
    g.user_id AS game_user_id,
    a.maxid,
    a.game_id AS a_game_id,
    modifiedAnswer.modified AS finished,
FROM
    games g

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

LEFT JOIN questions q ON (a.question_id = q.id)
LEFT JOIN sessions s ON (s.id = q.session_id)
LEFT JOIN sessiontypes st ON (st.id = s.sessiontype_id)
WHERE
    g.user_id = 21
GROUP BY
    g.id
HAVING
    (
        question_count = answer_count
    )
ORDER BY
    finished DESC;
Comments