0

I am trying to select multiple pieces of data from one column value on Table detail where the adjacent column on the same table is a specific value.

For example:

field_number | value
--------------------
       40    |  820
       41    |  548
       44    |  100

I need to return 3 select values all from detail.value such as to get the results that include:

"Balance Amount" | "Convo. Amount" | "Chapter ID"
 -------------------------------------------------
      820        |       100       |      548

This is all part of a larger query that joins across 3 tables to pull these values for multiple conditional values, I just do not know the best way to reference the same table.value with specific conditions for each return. I can supply my additional query syntax if it helps, but the above situation highlights the salient point. I also do not necessarily need to have the "Balance Amount", "Convo. Amount", and "Chapter ID" aliases if it further complicates an elegant solution.

Any help or insights you can provide would be greatly appreciated.

Mark Helmstetter
  • 85
  • 1
  • 6
  • the easyest way is to use subqueries but, if the query is very structured, this can slow execution. – genespos Nov 12 '15 at 16:47

2 Answers2

0

Something like this:

SELECT case when field_num=40 then value as 'Balance Amount' END,
case when field_num=41 then value as 'Convo. Amount' END,
case when field_num=44 then value as 'Chapter ID' END
from [table]

Would do it for you. But that's not very reliable unless you're positive that those are the values in field_num.

durbnpoisn
  • 4,666
  • 2
  • 16
  • 30
  • 1
    For MySQL (at least in v 5.5), you need to omit "value as", and there's an "else" clause you can also have after the last case. Doc at https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case – TextGeek Nov 22 '16 at 20:02
0

Not very optimized but you could use this. Note that value is a reserved word for MySQL.

SELECT 
    (SELECT `value` FROM detail WHERE field_number = 40) AS 'Balance Amount',
    (SELECT `value` FROM detail WHERE field_number = 44) AS 'Convo. Amount', 
    (SELECT `value` FROM detail WHERE field_number = 41) AS 'Chapter ID';

Result:

Balance Amount  Convo. Amount   Chapter ID
820             100             548
Kostas Mitsarakis
  • 4,772
  • 3
  • 23
  • 37