How to access outer column values in mysql subquery?

Question

In mysql I have this query

SELECT m.*
FROM members m
RIGHT JOIN (SELECT 
                IF(`from member_id`=1, `to member_id`, `from member_id`) as other_id, text, `date sent`
             FROM message
             WHERE ((`from member_id`=1 AND `to member_id`=m.id) OR (`to member_id`=1 OR `from member_id`=m.id))
             ORDER BY `date sent` DESC
             LIMIT 1
            ) as t on 1=1
ORDER BY t.`date sent` DESC

and I'm getting this error:

Unknown column 'm.id' in 'where clause'

How can I pass the members column value in the sub query select statement?

I am creating this sub query so it evaluates to 1 row, then I want to attach it to the right of the outer select statement.

Thanks.

What is this mess of a query supposed to do? it can probably be simplified. — ypercubeᵀᴹ, Aug 21 '13 at 22:12
I figured out how to do it, apparently what I wanted is known as the `groupwise maximum`. A good example of it is here http://stackoverflow.com/questions/15211479/groupwise-maximum. — omega, Aug 22 '13 at 13:35

score 0 · Accepted Answer · answered Aug 21 '13 at 20:43

You need to SELECT the from member_id/to member_id values in the subquery. Then, you can join the table m on the derived table where you will have access to the values.

) as t on t.`from member_id` = 1 AND t.`to member_id` = m.id
OR t.`to member_id` = 1 OR t.`from member_id` = m.id

How to access outer column values in mysql subquery?

1 Answers1