MySQL - need help with select statement

$sql = "SELECT budget_type AS TYPE, budget_group_id AS ID, rec_description AS DESCRIPTION, rec_frequency AS FREQUENCY, rec_total AS TOTAL, rec_yearly_total AS 'YEARLY TOTAL' FROM `budget_planner` WHERE user_id='5028'";


The select statement returns budget_group_id values of 1-10, is it possible to display the following words instead of the values via the select statement, or should I create a separate table with these values and then join them?

 

1  Income
2  Commitments
3  Home
4  Utilities
5  Education
6  Health
7  Shopping
8  Transport
9  Entertainment
sabecsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
It's not entirely clear (to me) what you want exactly - it is possible - e.g.
SELECT
      budget_type AS TYPE
    , budget_group_id AS ID
    , case when budget_group_id = 1 then 'Income'
           when budget_group_id = 2 then 'Commitments'
           ... <<more like this>>
           else rec_description
      end AS DESCRIPTION
    , rec_frequency AS FREQUENCY
    , rec_total AS TOTAL
    , rec_yearly_total AS 'YEARLY TOTAL'
FROM `budget_planner`
WHERE user_id = '5028'

Open in new window

but you would need to code this whenever you need it again (& such a case expression could be applied to budget_group_id instead or as well),

If it's used often either a view containing this logic - or maybe another table would probably be preferable. But why don't these descriptions exist in the source table? (wouldn't that be the best approach?)
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
Since they are not in the original table, creating one with the ids and names and joining it sounds like the right idea to me.  The alternative is to find a way to replace the id's with the names in the original table.
0
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
Hi!

I agree with DaveBaldwin. Creating a separate table with the budgetgroup_id and the names is more practical if you want to add, delete or change the group names on later stages. Change the names in the table leaving the application code untouched.

Don't forget to create indexes on the budgetgroup_id columns in both tables to speed up the queries.

Regards,
    Tomas Helgi
0
 
sabecsAuthor Commented:
Thanks for your input, much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.