INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Inverse of UNION results2

## Inverse of UNION results

(OP)
Hi Folks

I have 2 tables, which show time a particular event was activated, and time that event was cleared.

#### CODE

event_1
id 	activated 	cleared
1 	3 	9
2 	19 	26
3 	35 	46 

and

#### CODE

event_2
id 	activated 	cleared
1 	5 	10
2 	17 	22
3 	33 	47
4 	49 	50 

I want to find the bands of time that neither event_1 nor event_2 was active.
There could be more than 2 event tables.

I have tried manipulating UNION without success, and wonder if someone could point me in the right direction.

Many thanks

Graham

### RE: Inverse of UNION results

how are the times represented? are they simply representations of minutes in an hour (i.e. 0-59) or something else?

### RE: Inverse of UNION results

(OP)

They are just seconds, ultimately I will use unix timestamp.

In the examples above, my expected output is :-

1 to 3
10 to 17
26 to 33
47 to 49
50 onwards

Regards,

Graham

### RE: Inverse of UNION results

interestingly you are not defining your 'between' criterion inclusively. as id 1 of event_1 is activated at 3, thus I would count 3 out of the result set. likewise 10.

so that changes things a little.

assuming that all your values are in one table (which is by far the most logical table design) this structure would work

#### CODE --> once

<?php
mysql_query('create table seconds (sec id(2))');
$sql = 'insert into seconds (sec) values (%d)'; for($i=0;$i<60;$i++) mysql_query(sprintf($sql,$i));
?> 

you now have a table of seconds, ranged between 0 and 59.

for a unix timestamp based query you would move to a stored procedure that could take a start and end time as an argument and iterate through the seconds between them

#### CODE --> query

SELECT  sec
FROM    seconds s
WHERE
(
SELECT  COUNT(*) AS c
FROM    events_1
WHERE   s.sec > activated
AND
s.sec < cleared
) = 0
AND
(
SELECT  COUNT(*) AS c
FROM    events_2
WHERE   s.sec > activated
AND
s.sec < cleared
) = 0
GROUP BY s.sec ASC 

### RE: Inverse of UNION results

#### Quote:

There could be more than 2 event tables
That sounds like a questionable database design - if you add another type of event, you have to create a new table and code more SQL to cope with its appearance.

I'd suggest having a single table like this:

#### CODE

event_type  id   activated   cleared
event_1      1           3         9
event_1      2          19        26
event_1      3          35        46
event_2      1           5        10
event_2      2          17        22
event_2      3          33        47
event_2      4          49        50 

Also, if activated and created are date/timne values, use a date-related datatype for them instead of numbers.

Neither of these points address your actual question, I'm afraid, but getting the design right at the start will save a lot of heartache later on.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

### RE: Inverse of UNION results

(OP)

Many thanks for your quick response! I have tried the following - slight difference in table names - but no
output evident.

I am currently getting the data from individual tables for event_1 and event_2 as they are legacy and they have a lot of historic data in them I have a couple of test table on the local machine containing the data shown above in my original post.

The time span I am using at present is 0 to 60 seconds.

Not quite sure what I'm doing wrong...

#### CODE

<?php

$begin=0;$end=60;

//
mysql_query('create table seconds (sec id(2))');
$sql = 'insert into seconds (sec) values (%d)'; for($i=$begin;$i<$end;$i++) mysql_query(sprintf($sql,$i));
//

foreach($db->query('SELECT sec FROM seconds s WHERE ( SELECT COUNT(*) AS c FROM event_1 WHERE s.sec > activated AND s.sec < cleared ) = 0 AND ( SELECT COUNT(*) AS c FROM event_2 WHERE s.sec > activated AND s.sec < cleared ) = 0 GROUP BY s.sec ASC ') as$record)
{
$sec=$record['sec'];
echo \$sec.'<br>';
}
?> 

### RE: Inverse of UNION results

sorry. the create statement was wrong

#### CODE

create table seconds (sec INT(2))

remember - only run the create and the insert statements once.

### RE: Inverse of UNION results

(OP)
Looking good!

Many thanks indeed both, much appreciated!

Graham

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!