Simplified example:
select
ticket_id,
`number` as 'ticket number',
(SELECT count(*) from ost_thread_entry join ost_thread on ost_thread_entry.thread_id = ost_thread.id where ost_thread.object_id = 1234) as 'number of posts in ticket'
from
ost_ticket
I need to reference the value from ticket_id
instead of 1234
CodePudding user response:
You may use table aliases here:
SELECT
ticket_id,
number AS `ticket number`,
(SELECT COUNT(*)
FROM ost_thread_entry ote
INNER JOIN ost_thread ot ON ote.thread_id = ot.id
WHERE ot.object_id = t.ticket_id) AS `number of posts in ticket`
FROM ost_ticket t;
Note that you might also be able to write your query without the correlated subquery, instead using joins:
SELECT
t.ticket_id,
t.number AS `ticket number`,
COUNT(ote.thread_id) AS `number of posts in ticket`
FROM ost_ticket t
LEFT JOIN ost_thread ot ON ot.object_id = t.ticket_id
LEFT JOIN ost_thread_entry ote ON ote.thread_id = ot.id
GROUP BY
t.ticket_id,
t.number;