interesting MySQL query/view query :s

mjh · 03-24-2008, 01:12 PM

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?

TIA

Matt.

acid_kewpie · 03-24-2008, 02:25 PM

that's pretty simple if i'm reading you right, just use a case statement to progress through different options... http://dev.mysql.com/doc/refman/5.0/...statement.html

Code:

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, 07:22 AM

Perhaps this will explain a little better

I have a table:

Code:

+-------------------------+-----------+------+
| 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:

Code:

+-------------------------+--------------+
| 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, 07:30 AM

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