1

I have this MySQL statement:

SELECT a.id, a.`from member_id`, a.`to member_id`, IF(a.`from member_id`=1, a.`to member_id`, a.`from member_id`) as other_id, a.text, MAX(a.`date sent`) as `date sent`
FROM message a
JOIN members m on other_id=m.id
WHERE (a.`from member_id`=1 OR a.`to member_id`=1) AND a.active=1
GROUP BY other_id
ORDER BY other_id DESC, `date sent` DESC

but I am getting the error:

#1054 - Unknown column 'other_id' in 'on clause'

I am creating that column using the as key. Does anyone know whats wrong here?

Thanks.

omega
  • 40,311
  • 81
  • 251
  • 474

1 Answers1

2

The as creates a column alias (in this case other_id), and you can't join on a column alias. You can use an alias in the ORDER BY but nowhere else, unless the alias comes from a subquery.

Your best option here would be to repeat the IF function in the join:

SELECT
  a.id,
  a.from member_id,
  a.to member_id,
  IF(a.from member_id=1, a.to member_id, a.from member_id) as other_id,
  a.text,
  MAX(a.date sent) as date sent
FROM message a
JOIN members m on IF(a.from member_id=1, a.to member_id, a.from member_id) = m.id
WHERE (a.from member_id=1 OR a.to member_id=1) AND a.active=1
GROUP BY other_id
ORDER BY other_id DESC, date sent DESC
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69