CODEWITHSUNDEEP
mysql
Joel Garboden
Joel Garboden

Reputation: 71

Performing boolean logic on specific multiple rows in a mysql table/view

I've searched forums for a solution, but the examples aren't making sense to me. I know my syntax is incorrect, but it's the idea I'm trying to get across.

Assuming query was ran at 2012-03-09 24:00:00. (I think I edited dummy values correctly)

task_status is a view, running = (began > ended), ran=((now()-ended) < 10h)

select * from task_status;
+----------------+---------------------+---------------------+---------+------+
| task           | began               | ended               | running | ran  |
+----------------+---------------------+---------------------+---------+------+
| MIRRORS_SPLIT  | 2012-03-09 19:15:48 | 2012-03-09 19:15:51 |       0 |    1 |
| NETWORK_BACKUP | 2012-03-09 19:25:01 | 2012-03-09 19:23:41 |       0 |    1 |
| TAPE_BACKUP    | 2012-03-09 19:26:01 | 2012-03-09 23:16:32 |       0 |    1 |
+----------------+---------------------+---------------------+---------+------+

I'm having issues creating a BACKUPS row, began=(MIN(NETWORK_BACKUP.began, TAPE_BACKUP.began), and end=(MAX(NETWORK_BACKUP.end, TAPE_BACKUP.end). Running and ran should evaluate correctly then, right?

I want to add a row that results as such:

| BACKUPS        | 2012-03-09 19:25:01 | 2012-03-09 23:16:32 |       0 |    1 |

Any help would be greatly appreciated.

Upvotes: 0

Views: 129

Answers (2)

Joel Garboden
Joel Garboden

Reputation: 71

Thank you, JustDanyul, that got me on the right track.

Here's what I ended up using.

SELECT 0 =
  (
  SELECT 
  (
    (SELECT ran FROM expect.task_status WHERE task='NETWORK_BACKUP') 
    +
    (SELECT ran FROM expect.task_status WHERE task='TAPE_BACKUP')
  )
)

Upvotes: 0

JustDanyul
JustDanyul

Reputation: 14044

The way you use MIN() and MAX() seems wrong, remember, these are aggregate functions. They work on a set of values defined by content of the column you pass as a parameter.

It seems like you want them to tell you the MIN or MAX values of the values of began and end across two different rows, by passing two parameters. (unless I'm not understanding your pseudo syntax correctly).

Aren't you looking for something like

SELECT 'BACKUPS', MIN(began), MAX(ended) 
FROM task_status WHERE task = 'NETWORK_BACKUP' OR task = 'TAPE_BACKUP'
GROUP BY task

Upvotes: 2

Related Questions