How should I format my code to make it easier to read and understand in the future

Question

I am using this query to generate a conversation stream between user 184 and 192-

SELECT events.event_time, messages . *
FROM   events , messages
WHERE  events.global_id=messages.global_ref_id  AND 
       (messages.to =184 AND messages.from =192) OR
       events.global_id=messages.global_ref_id AND
       (messages.to =192 AND messages.from =184) AND 
       messages.global_ref_id < 495
ORDER BY `messages`.`global_ref_id` ASC

This query is working good for generating conversation between two users. Can you help me with converting this query to something more readable. Because I believe it is just a workaround and will cause some problems in future. Schema of messages table

Field         Type    
global_ref_id int(12)
to            int(12)
from          int(12)
message       text    
status        int(1)
viewed        int(1)

where global_ref_id is foreign key.

Answer 1

I prefer using JOIN...ON, clarifying an intention of priority of OR and AND phrase.

SELECT
  events.event_time,
  messages . *
FROM
  events
  JOIN messages ON events.global_id = messages.global_ref_id
WHERE
    ((messages.to =184 AND messages.from =192)
      OR (messages.to =192 AND messages.from =184))
    AND messages.global_ref_id < 495
ORDER BY
  messages.global_ref_id ASC

Answer 2

I would use a syntax like this:

SELECT events.event_time, messages .*
FROM   events , messages
WHERE  events.global_id=messages.global_ref_id 
AND ((messages.to = 184 AND messages.from = 192) OR (messages.to = 192 AND messages.from = 184))
AND messages.global_ref_id < 495
ORDER BY `messages`.`global_ref_id` ASC

or use a left outer join instead:

SELECT e.event_time, m.*
FROM   events e left outer join messages m ON e.global_id=m.global_ref_id 
WHERE ((m.to = 184 AND m.from = 192) OR (m.to = 192 AND m.from = 184))
AND m.global_ref_id < 495
ORDER BY m.`global_ref_id` ASC