Substitute MySQL result

Question

I'm getting the following data from a MySQL database

    +----------------+------------+---------------------+----------+
    | account_number | total_paid | doc_date            | doc_type |
    +----------------+------------+---------------------+----------+
    |             18 |    54.0700 | 2009-10-22 02:37:09 | IN       |
    |            425 |    49.9500 | 2009-10-22 02:31:47 | PO       |
    +----------------+------------+---------------------+----------+

The query is fine and I'm getting the data I need except that the doc_type isn't very human readable. To fix this, I've done the following

CREATE TEMPORARY TABLE doc_type (id char(2), string varchar(60));
INSERT INTO doc_type VALUES 
 ('IN', 'Invoice'),
 ('PO', 'Online payment'),
 ('PF', 'Offline payment'),
 ('CA', 'Credit adjustment'),
 ('DA', 'Debit adjustment'),
 ('OR', 'Order');

I then add a join against this temporary table so my doc_type column is easier to read which looks like this

+----------------+------------+---------------------+----------------+
| account_number | total_paid | doc_date            | document_type  |
+----------------+------------+---------------------+----------------+
|             18 |    54.0700 | 2009-10-22 02:37:09 | Invoice        |
|            425 |    49.9500 | 2009-10-22 02:31:47 | Online payment |
+----------------+------------+---------------------+----------------+

Is this the best way to do this? Is it possible to replace the text in one query? I started looking at if statements but it doesn't seem to be what I'm after or maybe I just read it incorrectly.

// EDIT //

Thanks everyone. I suppose I'll keep doing it this way.

Unfortunately, it's not possible to change doc_type to integer as this is an existing database for a billing application I didn't write. I'd end up breaking functionality if I made any changes other than adding a table here and there.

Also appreciate the easy to understand case statement from Rahul. May come in handy later.

Answer 1

It is the best way to do this :)

If doc_type could be an integer, you also can use ELT function, as in

SELECT ELT(doc_type, 'Invoice', 'Document') FROM table;

but it is still worse than simple join as you have to put this thing into every query and every application that using the database, and changing description becomes a hell.

Answer 2

Your current way is the best. Arguably, document_type can be changed to an int, to save space and whatnot, but that's irrelevant.

Doing the join will be much faster and readable than any chained ifs.

Not to mention, extensible. Should you need to add a new doc_type, it's just an insert vs. potentially several queries.

Answer 3

You can use the SQL CASE statement to do this in a single query.

Select account_number, total_paid, doc_date, 
       case doctype
           when 'IN' then 'Invoice'
           when 'PO' then 'Online Payment'
       end
from table

Answer 4

IIRC this is the correct way to achieve what you want to do. It's a normalized design

I think you are asking about the design and not how the data has to be fetched? If it is so, then I should tell I have always used the above kind of design.

This design leads to normalized database. There won't be consistency problems if you ever needed to change the name of the field like Invoice and Online Payment

I would suggest you to change doc_type field to int as not only it saves space(as told by Tordek) but it is also faster when you execute queries.

• Firstly.If you used Invoice in doct_type as string, then the problems could have been was that string search is extremely slow when compared to other datatypes.
• Second, it is case sensitive (which may lead to mistakes.
• Thirdly, since string takes up much space, so much more space is required for storing it in the main table.
• Fourth, If you ever required to change the name Invoice to say Billing, then searching for Invoice would take time and each and every row containing this value had to be updated