SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Thread: IF condition

  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IF condition

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

    PHP Code:
    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:

    PHP Code:
    SELECT FROM message_content mc
    LEFT JOIN message_status ms ON
    (ms.contentID mc.contentID)
    WHERE mc.contentID "1" AND
    IF(
    ms.status 2mc.created <= ms.lastUpdate 
    I Dunno LOL \(_o)/

  2. #2
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Code:
    IF(ms.status = 2) mc.created <= ms.lastUpdate
    is

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

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got error for this :'(

    PHP Code:
    SELECT FROM message_content mc
    LEFT JOIN message_status ms ON
    (ms.contentID mc.contentID)
    WHERE mc.contentID "1" AND
    IF(
    ms.status AND mc.created <= ms.lastUpdate
    I Dunno LOL \(_o)/

  4. #4
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Code:
    ( mc.contentID = "1" )
    OR
    ( ms.status = 2 AND mc.created <= ms.lastUpdate )
    Not sure if the rest of your logic is good...

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    I Dunno LOL \(_o)/

  6. #6
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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!

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    I Dunno LOL \(_o)/

  8. #8
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by justspree View Post
    I usually try to refrain from using "OR".
    You shouldn't.

    Quote Originally Posted by justspree View Post
    I always get weird results with that :P
    It's not OR's fault.

    Quote Originally Posted by justspree View Post
    Thanks again, appreciate your replies/help.
    No problem.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by itmitică View Post
    Code:
    ( 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Quote Originally Posted by itmitică View Post
    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)
    I Dunno LOL \(_o)/

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    I Dunno LOL \(_o)/

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code:
    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
    I Dunno LOL \(_o)/

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    could you please dump those test tables for me

    i'll need the CREATE TABLE statements, plus the INSERT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    I Dunno LOL \(_o)/

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you're welcome

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:
    Quote Originally Posted by itmitică View Post
    Code:
    ( 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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by itmitică View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Yeah, my last word again, is the same as the one above:

    Anyway, glad he sorted it out with your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •