SQL statement in mySQL and

Posted on 2007-08-09
Last Modified: 2010-04-23
Hello 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
Question by:ScottParker
    LVL 39

    Expert Comment

    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
    LVL 17

    Expert Comment

    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

    LVL 13

    Expert Comment

    Hi ScottParker

    try the following:

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

    Accepted Solution

    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
    LVL 3

    Author Comment

    Perfect.  Just what I was looking for.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now