CODEWITHSUNDEEP
mysqlsql
Gimali
Gimali

Reputation: 787

unexpected query result though data is correct

I've following two tables for a messaging feature -

message2_recips table :

mid seq uid status
1   1   1   N
1   1   503 A

And message2 table

mid seq created_on           created_on_ip  created_by  body
1   1   2013-08-08 19:17:44   1.2.2.1       503          some_random_text

I'm firing this query - 

SELECT m.mid, m.seq, m.created_ON, m.created_by, m.body, r.status 
        FROM message2_recips r
INNER JOIN message2 m ON m.mid=r.mid AND m.seq=r.seq 
WHERE r.uid=503 AND r.status in ('A', 'N') AND r.seq=(SELECT max(rr.seq) 
        FROM message2_recips rr 
WHERE rr.mid=m.mid AND rr.status in ('A', 'N')) AND IF (m.seq=1 AND m.created_by=503, 1=0, 1=1)
ORDER BY created_ON DESC

Expected Result is - 

mid seq created_on                  created_by  body                status
1   1   2013-08-08 19:17:44          503        some_random_text    A

But it returns zero results

But, if I remove if condition IF (m.seq=1 AND m.created_by=503, 1=0, 1=1) from query I get correct results. But condition in query, after observing data in message2 table satisfies and should work.

I can't figure out the possible reason. Any pointers regarding what's going wrong and how to correct will be very helpful.

The said query does following,

It gets conversation originated/received by current user, gets message content from other table. It gets message with status other than Delete which is Active or New. It filters our conversation with newest message with maximum sequence number. Each message does have own sequence number with increasing fashion.

This does work but as this is for Inbox feature of messaging, I want user to be able to see conversation initiated by himself. Right now with above query not working properly, he can see conversation initiated by him only after he receives a reply from other users.

Upvotes: 0

Views: 44

Answers (1)

Jim
Jim

Reputation: 22646

Your expected result matches the criteria for the IF:

IF (m.seq=1 AND m.created_by=503, 1=0, 1=1)

So the above expression returns 1=0 which is false so that row is ignored.

More readable may be to simply add this to the WHERE clause:

AND NOT (m.seq=1 AND m.created_by=503)

Remove the NOT if your intention was for the original IF to be true.

Upvotes: 2

Related Questions