Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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.

share|improve this question
3  
Please try making your title more descriptive. As it stands, users have no idea what this question contains. –  The Unfun Cat Nov 14 '12 at 9:37
    
What exactly do you not like about the query? It's perfectly correct. The only thing I would do is pull the global_ref_id check out and do it once. –  Aleks G Nov 14 '12 at 9:39
    
Actually In this query if I want to pull only those messages which have global_id greater than say 455 then what should I put the query. –  manishk3008 Nov 14 '12 at 9:51
add comment

migrated from stackoverflow.com Nov 16 '12 at 15:26

This question came from our site for professional and enthusiast programmers.

2 Answers

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
share|improve this answer
add comment
up vote 0 down vote accepted

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
share|improve this answer
    
Thanks for this.This worked to get results greater than 495. –  manishk3008 Nov 14 '12 at 9:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.