IF condition

[justspree]

Hi would appreciate some help here, basically I have 2 tables currently:

message_status
+----+------------+---------+------------------------+
|  id  |  contentID  |  status  |          lastUpdate          |
+----+------------+---------+------------------------+
|  1  |         1       |      2     | 2012-06-19 20:21:16     |
+----+------------+---------+------------------------+

message_content
+------------+----------+------------------------+
|  contentID  |  content  |          created              |
+------------+----------+------------------------+
|        1       |  testing    |  2012-06-19 20:21:00   |
+------------+----------+------------------------+
|        1       |  testing1  |  2012-06-19 20:21:16   |
+------------+----------+------------------------+
|        1       |  testing2  |  2012-06-19 20:21:30   |
+------------+----------+------------------------+

SELECT * FROM message_content mc
LEFT JOIN message_status ms ON(ms.contentID = mc.contentID)
WHERE mc.contentID = "1" 

When the status is 2, I want to only retrieve contents not more than the "lastUpdate" datetime. Is it possible to do this in a query with IF statement? For example:

SELECT * FROM message_content mc
LEFT JOIN message_status ms ON(ms.contentID = mc.contentID)
WHERE mc.contentID = "1" AND
IF(ms.status = 2) mc.created <= ms.lastUpdate 

[itmitică]

IF(ms.status = 2) mc.created <= ms.lastUpdate

is

( ms.status = 2 AND mc.created <= ms.lastUpdate )

[justspree]

Got error for this :'(

SELECT * FROM message_content mc
LEFT JOIN message_status ms ON(ms.contentID = mc.contentID)
WHERE mc.contentID = "1" AND
IF(ms.status = 2 AND mc.created <= ms.lastUpdate) 

[itmitică]

( mc.contentID = "1" )
OR
( ms.status = 2 AND mc.created <= ms.lastUpdate )

Not sure if the rest of your logic is good...

[justspree]

I'm sorry itmitică, don't think you are correct in this. IF statement in MySQL needed more paramters than that. Something like

IF(ms.status = 2 AND mc.created <= ms.lastUpdate, 1, 0)

I just don't quite understand on how to implement it. Ruby you around or anyone with experience in this?

[itmitică]

SELECT * FROM message_content mc
LEFT JOIN message_status ms ON(ms.contentID = mc.contentID)
WHERE ( mc.contentID = "1" )
OR
( ms.status = 2 AND mc.created <= ms.lastUpdate )

I thought it was clear I'm suggesting a solution without IF.

Again, not sure if your thinking is on the right track with this. And I believe is Rudy you're calling out for!

[justspree]

Thanks itmitică! It seems to work, hopefully no hiccup. I usually try to refrain from using "OR". I always get weird results with that :P

P.S: Ahh yes, Rudy.. lol

Thanks again, appreciate your replies/help.

[itmitică]

I usually try to refrain from using "OR".

You shouldn't.

I always get weird results with that :P

It's not OR's fault.

Thanks again, appreciate your replies/help.

No problem.

[r937]

( mc.contentID = "1" )
OR
( ms.status = 2 AND mc.created <= ms.lastUpdate )

Not sure if the rest of your logic is good...

not sure the logic here is any good either

if ms.status = 2 AND mc.created <= ms.lastUpdate, you will get a bunch of content, regardless of which contentID it's for

my understanding is, it has to be for contentID = 1

hence, the OR is wrong



spree, you used a left outer join... is it possible to have a message_content row for a contentid that has no match in the message_status table? or will there always be a status for each contentid in message_content?

in that case, it should be an inner join

Off Topic:

by the way, any relation to winston spree simon?

[justspree]

Aha! Rudy to the rescue! Just when I saw the "logic error" whereby I am now getting a bunch of content, regardless of which contentID *ouch!

Yes Rudy, there is bound to be one status row for each contentid. How should I do this with inner join? Do i need IF statement for that? If the status = 2, i'd want to do a comparison, something like mc.created <= ms.lastUpdate. So as I don't want to retrieve content later than the lastUpdate, is it possible?

Off Topic:

by the way, any relation to winston spree simon?

Erm no, i duno who is that :P

You shouldn't.
It's not OR's fault.
No problem.

See itmitică? This is what I meant by weird results lol. (Not really that weird but OR always makes you think differently in the brain :P)

[r937]

Yes Rudy, there is bound to be one status row for each contentid.

so, like, at most one? or possibly more than one?

but more importantly, what happens if the status is not 2? do you want any content rows at all?

i know you said if the status is 2, don't return content that is after the status last update, but ...

[justspree]

There will only be one status row. If the status is 1, then no comparison needed, means all active. 2 means only retrieve the content stopping at the lastUpdate point.

[r937]

There will only be one status row. If the status is 1, then no comparison needed, means all active. 2 means only retrieve the content stopping at the lastUpdate point.

beauty

try this --

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star 
  FROM message_status AS ms
INNER
  JOIN message_content AS mc
    ON mc.contentID = ms.contentID
   AND mc.created <=
       CASE WHEN ms.status = 2
            THEN ms.lastUpdate
            ELSE mc.created END

if ms.status isn't 2, then the AND condition is mc.created <= mc.created, which is always true, so you get all content rows

[justspree]

The statement looks nice and valid Rudy! But weird..its still retrieving content later than lastUpdate, plus the status column is showing "2" for those content earlier than lastUpdate while the content which shouldn't be showing becomes "1". Also the lastUpdate date becomes null. Any idea?

Result:
+-------------+-----------+------------------------+-----------+------------------------+
|  contentID  |  content  |          created       |  status   |    lastUpdate          |
+-------------+-----------+------------------------+-----------+------------------------+
|        1    |  testing  |  2012-06-19 20:21:00   |     2     |  2012-06-19 20:21:16   |
+-------------+-----------+------------------------+-----------+------------------------+
|        1    |  testing1 |  2012-06-19 20:21:16   |     2     |  2012-06-19 20:21:16   |
+-------------+-----------+------------------------+-----------+------------------------+
|        1    |  testing2 |  2012-06-19 20:21:30   |     1     |     NULL               |
+-------------+-----------+------------------------+-----------+------------------------+

SELECT mc.contentID, mc.content, mc.created, ms.status, ms.lastUpdate
  FROM message_status AS ms
INNER
  JOIN message_content AS mc
    ON mc.contentID = ms.contentID
   AND mc.created <=
       CASE WHEN ms.status = 2
            THEN ms.lastUpdate
            ELSE mc.created END

[r937]

could you please dump those test tables for me

i'll need the CREATE TABLE statements, plus the INSERT statements

[justspree]

could you please dump those test tables for me

i'll need the CREATE TABLE statements, plus the INSERT statements

Hey rudy!!! Got it working!! Sorry some typo on my end!

Thank you very much for your expertise! Learn something new from you everytime I come here! Never had tried using Case When Then Else stuff like that ;p

[r937]

you're welcome

[itmitică]

not sure the logic here is any good either

if ms.status = 2 AND mc.created <= ms.lastUpdate, you will get a bunch of content, regardless of which contentID it's for

my understanding is, it has to be for contentID = 1

hence, the OR is wrong

No, the OR is right. There's no good logic reason one would need to avoid OR.

The OP logic however... That's why:

( mc.contentID = "1" )
OR
( ms.status = 2 AND mc.created <= ms.lastUpdate )

Not sure if the rest of your logic is good...

with emphasis on "Not sure if the rest of your logic is good"

Anyway, glad he sorted it out with your help.

[r937]

No, the OR is right.

sorry, you are wrong

in this particular case, the OR was wrong

demonstrably, as in "q.e.d."



and now, of course, you will want the last word again, so please, do reply once more, lol

[itmitică]

Yeah, my last word again, is the same as the one above:

Anyway, glad he sorted it out with your help.