• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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
0
sabecs
Asked:
sabecs
3 Solutions
 
Dave BaldwinFixer 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
 
PortletPaulfreelancerCommented:
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
 
Tomas Helgi JohannssonCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now