Not sure if the rest of your logic is good...[/QUOTE]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
—
2012-06-23T02:28:02Z —
#10

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

itmitică;5140480 said:

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
—
2012-06-23T02:38:52Z —
#11

justspree said:

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
—
2012-06-23T02:43:18Z —
#12

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
—
2012-06-23T03:01:52Z —
#13

justspree said:

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.

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
—
2012-06-23T03:46:06Z —
#14

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?