Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL statement in mySQL and  VB.net

Posted on 2007-08-09
5
Medium Priority
?
360 Views
Last Modified: 2010-04-23
Hello
VB.net 2005
mySQL 4.1 backend database

I have the following sql statement
Select userid, catcode01, firstname, lastname from users where userid = 5
the value that is stored in the user table for catcode01 is a number 1-10.
Without making another table, and doing a join to it, is there a way in the SQL statement to show some text instead of or in addition to the catcode01 field?

example returned record
123     1  john doe
124     1  jane doe
125     2  frank smith
what I would like it to show is..
123 1-Sales john doe
124 1-Sales jane doe
125 2-Accounting frank smith
0
Comment
Question by:ScottParker
5 Comments
 
LVL 39

Expert Comment

by:appari
ID: 19667278
may be something like this

Select userid, if(catcode01=1,'1-Sales', if(catcode01=2, '2-Accounting','Other') ) catDEtail, , firstname, lastname from users where userid = 5
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 19667296
Try this

Select userid+' '+case catcode01 when 1 then 'Sales' when 2 then 'Accounting' else 'unknown' end +' '+firstname+' '+ lastname from users where userid = 5

You can add more value of catcode01

HuyBD;
0
 
LVL 13

Expert Comment

by:iHadi
ID: 19667306
Hi ScottParker

try the following:

SELECT CONCAT( userid, " ", catcode01, " ", firstname, " ", lastname) FROM users WHERE userid = 5
0
 
LVL 17

Accepted Solution

by:
HuyBD earned 2000 total points
ID: 19667322
sorry, I have missing


Select concat(userid,' ',case catcode01 when 1 then 'Sales' when 2 then 'Accounting' else 'unknown' end ,' ',firstname,' ', lastname) from users where userid = 5
0
 
LVL 3

Author Comment

by:ScottParker
ID: 19667362
Perfect.  Just what I was looking for.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question