44768

MySQL select column which is a value in another column

Question:

my sql table is something like this:

+----+------------+--------+---------------+---------------+-------------+----------+-------------+---------------------+ | id | msisdn | status | reason | CallRejection | Unavailable | NoAnswer | NetworkBusy | autotimestamp | +----+------------+--------+---------------+---------------+-------------+----------+-------------+---------------------+ | 1 | 9999999901 | Failed | NoAnswer | 1 | 2 | 2 | 4 | 2013-12-19 15:46:50 | | 2 | 9999999902 | Failed | NetworkBusy | 4 | 2 | 3 | 2 | 2013-12-19 13:07:42 | | 3 | 9999999903 | Failed | CallRejection | 6 | 6 | 6 | 5 | 2013-12-19 13:07:53 | | 4 | 9999999904 | Failed | Unavailable | 2 | 4 | 2 | 0 | 2013-12-19 13:08:03 | | 5 | 9999999905 | Failed | Misc | 6 | 2 | 6 | 2 | 2013-12-19 13:07:01 | | 6 | 9999999906 | Failed | NoAnswer | 1 | 3 | 1 | 6 | 2013-12-19 13:07:24 | | 7 | 9999999907 | Failed | NetworkBusy | 6 | 6 | 2 | 3 | 2013-12-19 13:07:42 | | 8 | 9999999908 | Failed | CallRejection | 8 | 3 | 3 | 0 | 2013-12-19 13:07:53 | | 9 | 9999999909 | Failed | Unavailable | 3 | 8 | 5 | 6 | 2013-12-19 13:08:03 | | 10 | 9999999910 | Failed | Misc | 8 | 4 | 0 | 4 | 2013-12-19 13:07:01 | +----+------------+--------+---------------+---------------+-------------+----------+-------------+---------------------+

I want to extract the value of each reason based on the value of reason column for each row. eg:

row 1 should give 2 as a result because the reason column has noanswer, so the value of that particular column i.e. noanswer should be returned.

Actually, I have to select multiple rows after comparing the returned valued with a constant. I'm able to do this by the following method :

select @col := reason from msisdn_table where id=1; SET @a = concat('select ', @col, ' from msisdn_table where id=1'); PREPARE STMT FROM @a; EXECUTE STMT; DEALLOCATE PREPARE STMT;

But this is not very promising solution because it is for only one row and i'll have to use a for loop in order to get chunk's of 1000 rows, and that would not be very efficient. What is the more efficient way?

Answer1:

Try this:

SELECT id, reason CASE WHEN reason = 'NoAnswer' THEN NoAnswer WHEN reason = 'NetworkBusy' THEN NetworkBusy END AS value FROM table1

It should give you an output like this:

+----+------------+----------+ | id | reason | value | +----+------------+----------+ | 1 | NoAnswer | 2 | +----+------------+----------+

Recommend

  • Proguard does not obfuscate gui components
  • Asp controls Id generation inside repeater
  • Java error using JARs, java.lang.NoClassDefFoundError: javax/mail/Store
  • C++/C# Float issues
  • How to extract a number from a string [duplicate]
  • How to use the resource module to measure the running time of a function?
  • Google Places API - Find a company's CID and LRD
  • Groovy: Unexpected token “:”
  • Caching attributes in superclass
  • Converting a WriteableBitmap image ToArray in UWP
  • Use of this Javascript
  • Replace value with Factor in r data.table
  • Is it possible to access block's scope in method?
  • Extracting HTML between tags
  • C++ Partial template specialization - design simplification
  • SignalR .NET Client Invoke throws an exception
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Linq Objects Group By & Sum
  • Android screen density dpi vs ppi
  • Optimizing database types to compact database (SQLite)
  • Bug in WPF DataGrid
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • php design question - will a Helper help here?
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Python: how to group similar lists together in a list of lists?
  • Buffer size for converting unsigned long to string
  • AngularJs get employee from factory
  • WPF Applying a trigger on binding failure
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Can't mass-assign protected attributes when import data from csv file
  • reshape alternating columns in less time and using less memory
  • Unable to use reactive element in my shiny app
  • java string with new operator and a literal