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
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)
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!
( mc.contentID = "1" )
OR
( ms.status = 2 AND mc.created <= ms.lastUpdate )
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?
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
[QUOTE=itmitică;5140480]You shouldn’t.
It’s not OR’s fault.
No problem. :)[/QUOTE]
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)
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.
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
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
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