Reference and calculate data in the same query

Question

I have a table which stores actions. There's two special types of action, let's say for example, "Wake up" and "Go to bed".

All actions BETWEEN "Wake up" and "Go to bed" should return an extra field with the value "AWAKE". If the action is before "Wake up" or after "Go to bed", it should return an extra field with the value "ASLEEP". (These are just example values, try not to take them too seriously.)

+--------+-----------+
|  ac_id |  ac_name  |
+--------+-----------+
| 1      |   Talk    |
+--------+-----------+
| 2      |  Wake Up  |
+--------+-----------+
| 3      |   Walk    |
+--------+-----------+
| 4      | Go to bed |
+--------+-----------+
| 5      |  Scream   |
+--------+-----------+

With this structure, if I make a query with SELECT ac_name, ac_status FROM actions_table WHERE ac_name = 'Walk' it should return :

ac_name: Walk
ac_status: AWAKE

...because "Walk" is between "Wake Up" and "Go to bed".

My question is how, in the same query and without making any extra queries, I can return that value depending on the "position" of the row?

It's possible I am demanding too much on MySQL, but if I can do so I'd like to know how. If this is not possible in a single query, STORE PROCEDURES or FUNCTIONS are accepted too.

EDIT There can be more than one "Wake Up" and more than one "Go to bed" in the table.

Fabien TheSolution · Accepted Answer · 2013-07-25 17:37:35Z

1

You can try something like this :

SELECT ac_name, 'AWAKE' as ac_status 
FROM actions_table 
WHERE ac_id > (SELECT ac_id FROM actions_table WHERE ac_name = 'Wake Up') AND 
    ac_id < (SELECT ac_id FROM actions_table WHERE ac_name = 'Go to bed') 
UNION
SELECT ac_name, 'ASLEEP' as ac_status 
FROM actions_table 
WHERE ac_id < (SELECT ac_id FROM actions_table WHERE ac_name = 'Wake Up') OR 
    ac_id > (SELECT ac_id FROM actions_table WHERE ac_name = 'Go to bed'

Results are :

ac_name  |  ac_status
_________|___________
Scream   |  ASLEEP
Talk     |  ASLEEP
Walk     |  AWAKE

EDIT :

May be more this one for a single result :

SELECT ac_name, 
CASE 
WHEN ac_id > (SELECT ac_id FROM actions_table 
                   WHERE ac_name = 'Wake Up') AND 
    ac_id < (SELECT ac_id FROM actions_table 
             WHERE ac_name = 'Go to bed') 
THEN 'AWAKE' 
WHEN ac_id < (SELECT ac_id FROM actions_table 
                   WHERE ac_name = 'Wake Up') OR 
    ac_id > (SELECT ac_id FROM actions_table 
             WHERE ac_name = 'Go to bed') 
THEN 'ASLEEP' 
ELSE 'NO STATUS' END as ac_status 
FROM actions_table 
WHERE ac_name = 'Walk'

edited Jul 25, 2013 at 17:37

answered Jul 25, 2013 at 16:43

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Unapedra Over a year ago

Thanks so much for the answer! It should be the way, I think. However - I edited the question - maybe there are more than one "Wake Up" and more than one "Go to bed", so the references to the ac_id could return more than one result. Anyway, I'll try to play with your answer to get to the point. Thank you very much!

Praveen Prasannan · Accepted Answer · 2013-07-26 10:20:58Z

0

My suggestion is to create a table ac_status_master having fields ac_name and corresponding ac_name. Then you can do a simple join to get the desired result.

+--------+-----------+
| ac_name| ac_status |
+--------+-----------+
| awake  |   Talk    |
+--------+-----------+
| awake  |  Wake Up  |
+--------+-----------+
| awake  |   Walk    |
+--------+-----------+
| asleep | Go to bed |
+--------+-----------+
| asleep |  Scream   |
+--------+-----------+

answered Jul 26, 2013 at 10:20

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

1 Comment

Unapedra Over a year ago

Thanks for your answer. This is a valid solution, but this case is very simplified here. In fact, I wanted to get the way in order to be able to apply it in my real case, where the database structure is so much more complicated. Doing as you suggest would mean everytime I change an action position (let's say, I want to put "Walk" after "Go to bed"), I would have to update all the related tables. However, thanks again for your answer!

Collectives™ on Stack Overflow

Reference and calculate data in the same query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related