Help with creating correct CASE statement
I'm having problems figuring out how to create a case statement to the query I copied below. Essentially what the query does is only pull rows from the messages_messages query the current user sent ($userid), or the sender of the original thread ($threadid) sent. That's the perfect SELECT for everyone except the original sender of the thread, who should be able to pull all the messages related to the thread.
Basically I need to add the SQL equivalent of the following statement:
WHERE messages_threads.id = $threadid
if($userid == messages_threads.senderid) {
// Don't add an AND clause
} else {
// Add the follow开发者_Go百科ing AND clause
AND (messages_messages.senderid = $userid OR messages_messages.senderid = messages_threads.senderid)
}
Here's the query I'm currently using:
SELECT messages_messages.id, messages_messages.senderid, username AS sendername, pictures.url AS picture, body, UNIX_TIMESTAMP(time) AS unixtimestamp
FROM messages_messages
JOIN messages_threads ON messages_threads.id = messages_messages.threadid
JOIN users ON messages_messages.senderid = users.id
LEFT JOIN pictures ON messages_messages.senderid = pictures.userid AND pictures.profile = 1
WHERE messages_threads.id = $threadid
AND (messages_messages.senderid = $userid OR messages_messages.senderid = messages_threads.senderid)
ORDER BY time ASC
Any series of if statements can be converted into ANDs and ORs:
WHERE messages_threads.id = $threadid AND
($userid=messages_threads.senderid OR
(messages_messages.senderid = $userid OR
messages_messages.senderid = messages_threads.senderid))
if $userid==messages_threads.senderid
is true then the whole or
clause is true, so nothing else is checked. If it's false, then the rest of the clause is checked.
精彩评论