...

View Full Version : mysql SELECT with IF statement



Trki
04-24-2013, 03:56 PM
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...

http://i.stack.imgur.com/V6FzD.png

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.

Old Pedant
04-24-2013, 08:35 PM
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:

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


id user_id status shared_from_id
12164 4310 xxxxxx 12164

Or what?

Old Pedant
04-24-2013, 08:43 PM
I *THINK* what you want is this:



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:


12165 4310 status12165 12164
12164 25 status12164 null
12163 4310 status12163 null

I believe that query would output


12164 status12164
12163 status12163

which I *think* is what you want. No?

Trki
04-24-2013, 09:46 PM
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:

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


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)*

Trki
04-24-2013, 09:50 PM
I *THINK* what you want is this:



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:


12165 4310 status12165 12164
12164 25 status12164 null
12163 4310 status12163 null

I believe that query would output


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 ) ?

Old Pedant
04-24-2013, 10:09 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum