开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜