Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    mysql SELECT with IF statement

    Hi i have easy function like this:

    (25 is only random user id for example)

    SELECT * FROM statuses WHERE user_id=4310

    Works okay. To retrieve my statuses WHERE USER ID is MY(USER) ID... but now i need to make it more complex... i am working on "sharing statuses" with this DB...



    Whats the best way to do it? I am pretty confused... i have in mind something like... select from DB where user id is my ... but if shared from is not null .. select instead status with id = shared from id... or i dont know .. something like that.

    How would you do that? And yes i can modify the DB table little be if i need to add some helping column or something.

    PERFORMANCES ARE ALSO IMPORTANT!

    I am trying do something like:

    SELECT
    st.*

    FROM statuses AS st WHERE
    IF(st.shared_from_id is null, st.user_id=4310, st.id=st.shared_from_id)

    ORDER BY st.id DESC LIMIT 15

    But it doesnt select the extra row i need.
    Last edited by Trki; 04-24-2013 at 03:24 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    First of all, *NEVER* use SELECT *, and that's especially important when you want to select values based on conditions.

    It would have helped if you had simply told us what you wanted to see for the record with id 12165.

    This IF makes no sense to me:
    Code:
    IF(st.shared_from_id is null, st.user_id=4310, st.id=st.shared_from_id)
    That seems to say that if shared_from_id IS NULL, then the user_id remains untouched. But if it is not null, then the ID itself--not the user_id--is changed. Meaning that no matter what the user_id will be 4310. HUH?

    Did you want to see
    Code:
    id    user_id status shared_from_id
    12164    4310 xxxxxx 12164
    Or what?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    I *THINK* what you want is this:

    Code:
    SELECT IFNULL(s2.id,s1.id) AS effectiveID, IFNULL(s2.status, s1.status) AS effectiveStatus
    FROM statuses AS s1
    LEFT JOIN statuses AS s2 ON s2.id = s1.shared_from_id
    WHERE s1.user_id = 4310
    ORDER BY effectiveID DESC
    LIMIT 15
    Given data like this:
    Code:
    12165  4310  status12165  12164
    12164    25  status12164  null
    12163  4310  status12163  null
    I believe that query would output
    Code:
    12164  status12164
    12163  status12163
    which I *think* is what you want. No?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    First of all, *NEVER* use SELECT *, and that's especially important when you want to select values based on conditions.

    It would have helped if you had simply told us what you wanted to see for the record with id 12165.

    This IF makes no sense to me:
    Code:
    IF(st.shared_from_id is null, st.user_id=4310, st.id=st.shared_from_id)
    That seems to say that if shared_from_id IS NULL, then the user_id remains untouched. But if it is not null, then the ID itself--not the user_id--is changed. Meaning that no matter what the user_id will be 4310. HUH?

    Did you want to see
    Code:
    id    user_id status shared_from_id
    12164    4310 xxxxxx 12164
    Or what?
    I meaned it like: select from statuses where if shared from id is null *use the user id*, else *select the id of the owner of shared status so where the status.id = 12164(shared_from_id)*

  • #5
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I *THINK* what you want is this:

    Code:
    SELECT IFNULL(s2.id,s1.id) AS effectiveID, IFNULL(s2.status, s1.status) AS effectiveStatus
    FROM statuses AS s1
    LEFT JOIN statuses AS s2 ON s2.id = s1.shared_from_id
    WHERE s1.user_id = 4310
    ORDER BY effectiveID DESC
    LIMIT 15
    Given data like this:
    Code:
    12165  4310  status12165  12164
    12164    25  status12164  null
    12163  4310  status12163  null
    I believe that query would output
    Code:
    12164  status12164
    12163  status12163
    which I *think* is what you want. No?
    yea exactly! you are genious nice! self join came to mind me too but i didnt know how to write it. And now when i want to display all other fields how to do that? its prety large table so ... should i allways... IFNULL(etc etc ) ?

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    If all the other fields need to come from the "s2" table when they exist, then yes, you would need to use IFNULL on all of them, as well.

    But for fields that can come from "s1", just list the field names without the IFNULL.

    I know it is a lot of writing, but doing it this way will give the best possible performance. And you only have to write it once, at least.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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