[SOLVED] Multiple where conditions?

[Dan06]

How can I have a mysql query with 3 (or more) Where clauses (I know Where only takes 2). The following code doesn't work, but it demonstrates what I'd like to achieve:

 

SELECT msgSender, message_content.msgId, msgDate, msgContent
FROM utility.message_inbox, utility.message_content
WHERE msgRecipient = "o4jd9sk1"
AND message_inbox.msgId = message_content.msgId
AND message_content.msgChild = "NULL" 

[Zane]

Use parentheses?

 

SELECT msgSender, message_content.msgId, msgDate, msgContent
FROM utility.message_inbox, utility.message_content
WHERE 
(msgRecipient = "o4jd9sk1" AND message_inbox.msgId = message_content.msgId)
AND message_content.msgChild = "NULL"

 

or

 

 

SELECT msgSender, message_content.msgId, msgDate, msgContent
FROM utility.message_inbox, utility.message_content
WHERE 
msgRecipient = "o4jd9sk1" AND (message_inbox.msgId = message_content.msgId
AND message_content.msgChild = "NULL")

 

it's all about precedence.

[Dan06]

I used parentheses as shown below:

SELECT msgSender, message_content.msgId, msgDate, msgContent
FROM utility.message_inbox, utility.message_content
WHERE
msgRecipient = "o4jd9sk1" AND (message_inbox.msgId = message_content.msgId
AND message_content.msgChild = "NULL")

 

but an empty result set was returned. It seems that the cause of the problem is the: AND message_content.msgChild = "NULL" statement. When I changed the aforementioned statement to: AND message_content.msgId = "1" the result set showed the expected content.

 

Currently, the msgChild field is by default set to NULL, it's supposed to indicate that the current message is the last one in the thread since it has no children. Is the error in my statement or elsewhere?

[Mchl]

zanus: What's the point in using parentheses for ANDs?

 

Dan06: Use AND message_content.msgChild IS NULL

[Dan06]

Thanks, changing = to IS resolved the issue.

[Zane]

Yeah it probably would make more sense to use OR with parentheses.

SELECT msgSender, message_content.msgId, msgDate, msgContent
FROM utility.message_inbox, utility.message_content
WHERE
(msgRecipient = "o4jd9sk1" AND message_inbox.msgId = message_content.msgId)
OR message_content.msgChild = "NULL"

 

@Mchl, I'm not too sure.  the op said he wasn't able to use 3 where clauses so the first thing that came to mind was..Parentheses!  I spoke to soon I guess.  I'm definitely not the brightest SQL tool in the shed, but I can make my way around.

[Mchl]

Logical conjunction (AND) and alternative (OR) are both associative

[tex]a \land (b \land c) \equiv (a \land b) \land c [/tex]

[tex]a \lor (b \lor c) \equiv (a \lor b) \lor c [/tex]

so as long as there are only ORs or only ANDs, there is no need to use parentheses.

 

(tex code shamelessly ripped off Wikipedia )