LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 03-24-2008, 01:12 PM   #1
mjh
Member
 
Registered: Nov 2007
Posts: 37

Rep: Reputation: 15
Question 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?

TIA

Matt.
 
Old 03-24-2008, 02:25 PM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,395

Rep: Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963
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?
 
Old 03-25-2008, 07:22 AM   #3
mjh
Member
 
Registered: Nov 2007
Posts: 37

Original Poster
Rep: Reputation: 15
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?
 
Old 03-25-2008, 07:30 AM   #4
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,395

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


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql use output of one query in another query secretlydead Programming 2 11-19-2007 01:25 AM
MySQL 5.0: Can I retrieve the query used to create a view? Robhogg Linux - Server 2 02-13-2007 06:29 PM
help with mysql query: return nth rows in query hawarden Programming 2 07-31-2006 06:36 PM
MySQL++ Query jimbob8483 Programming 1 12-15-2004 06:47 AM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 12:27 PM


All times are GMT -5. The time now is 12:45 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration