-   Programming (
-   -   interesting MySQL query/view query :s (

mjh 03-24-2008 02:12 PM

interesting MySQL query/view query :s
I want to create a view of a list of members that haven't given us specific details yet, but I want a fake column to exist in the view that will change according to the value of one and/or two other columns to indicate where the problem lies.

The list of members among other things keeps track of a BOS and a UCAS number; the default for each is 0000 in case a member doesn't have either with them when they sign up.

If a member's UCAS is set to 0000 I want the field prob to be set to UCAS. If the member's BOS is set to 0000 I want the field prob to be set to BOS. And if both UCAS and BOS are set to 0000 then the prob field should be set to UCAS and BOS.

I want this to be MySQL specific if possible, cos I like to do a lot of stuff on the command line, and don't want to have to rely on PHP to find out what user has what problem.

Does anyone have any ideas?



acid_kewpie 03-24-2008 03:25 PM

that's pretty simple if i'm reading you right, just use a case statement to progress through different options...


SELECT CASE WHEN ucas = '0000' THEN ucas WHEN bos = '0000' THEN bos ELSE 'something else' END AS newfield FROM ....
but your examples don't seem to make much sense, so this code mightn't either... if you only check for a number being 0000 why would you then print that value?

mjh 03-25-2008 08:22 AM

Perhaps this will explain a little better

I have a table:


| realName                | UCAS      | BOS  |
| Nia                    | 0000      | 0000 |
| Jemma                  | 053183570 | 0473 |
| Kevin                  | 073321202 | 0000 |
| Annette                | 064428725 | 1438 |
| Debbi                  | 0000      | 0886 |
| Zoe                    | 052203543 | 0000 |

You can see here that a lot of people haven't in putted their BOS number, and someone hasn't entered their UCAS number.

What I want is to create a view that would output the following:


| realName                | problem      |
| Nia                    | UCAS and BOS |
| Kevin                  | BOS          |
| Debbi                  | UCAS        |
| Zoe                    | BOS          |

You can see that Annette and Jemma are not included because they have correct details. Nia hasn't given us any details, so it says so - Kevin, Debbi and Zoe should be self-explanitory.

Does that help?

acid_kewpie 03-25-2008 08:30 AM

ok, so just modify what i gave you above then, with each outcome covered.

All times are GMT -5. The time now is 06:27 PM.