# Thread: How many values are present in two intervals across several tables?

1. ## How many values are present in two intervals across several tables?

Hello there,

I am writing again, because last time I wrote on this forum, I had great support. Thank you.

I am trying to make a query where I count the number of id that are present in an unixTimestamp interval and at the same time present in another unixTimestamp interval from a series of tables

I.e How many id that are present where unixTimestamp > 1266428321 in "table 1" and "table 2" that is also present where unixTimestamp < 1266428321 in any of the tables.

Not how many rows, but how many unique ids? The answer is three (id: 1 & id: 2 & id 3)

The question:

How do I write the query?

Code:
```Table 1
+-------+---------------+
|  id  	| unixTimestamp |
+-------+---------------+
|       |               |
|     1 |   1266416813  |
|     1 |   1266416921  |
|     3 |   1266418721  |
|     2 |   1266420471  |
|     4 |   1266428321  |
|     1 |   1266429921  |
|     3 |   1266430821  |
|       |               |
+-------+---------------+

Table 2
+-------+---------------+
|  id  	| unixTimestamp |
+-------+---------------+
|       |               |
|     3 |   1266416814  |
|     1 |   1266416921  |
|     3 |   1266418721  |
|     5 |   1266420471  |
|     4 |   1266428321  |
|     1 |   1266429921  |
|     2 |   1266430821  |
|       |               |
+-------+---------------+```

Kind regards,
Marius

2. Supposing "table 1" has the name ts1 and "table 2" has the name ts2, this query works for me:

Code MySQL:
```SELECT DISTINCT
tmp.id
FROM
(
SELECT id
FROM ts1
WHERE unixTimestamp < 1266428321
UNION
SELECT id
FROM ts2
WHERE unixTimestamp < 1266428321
)
AS tmp
WHERE
id IN
(
SELECT id
FROM ts1
WHERE unixTimestamp > 1266428321
UNION
SELECT id
FROM ts2
WHERE unixTimestamp > 1266428321
)```

I'm pretty sure there are better (shorter, more efficient) queries possible though ...

3. Originally Posted by ScallioXTX
Supposing "table 1" has the name ts1 and "table 2" has the name ts2, this query works for me:

Code MySQL:
```SELECT DISTINCT
tmp.id
FROM
(
SELECT id
FROM ts1
WHERE unixTimestamp < 1266428321
UNION
SELECT id
FROM ts2
WHERE unixTimestamp < 1266428321
)
AS tmp
WHERE
id IN
(
SELECT id
FROM ts1
WHERE unixTimestamp > 1266428321
UNION
SELECT id
FROM ts2
WHERE unixTimestamp > 1266428321
)```

I'm pretty sure there are better (shorter, more efficient) queries possible though ...
Yes, that query seems to preform what I need.

I have two questions:

1. Can I make it count the num of id's instead of returning a list?
2. Does somebody know a shorter, more efficient way? :P

Thanks,
Marius

4. Untested

Code SQL:
```SELECT
t.id
,SUM(t.total) total
,t.select_num
FROM
(SELECT
id
,COUNT(*) total
,1 select_num
FROM
table1
WHERE
unixTimestamp > 1266428321
GROUP
BY
id
UNION ALL
SELECT
id
,COUNT(*)
,4
FROM
table2
WHERE
unixTimestamp > 1266428321
GROUP
BY
id
UNION ALL
SELECT
id
,COUNT(*)
,2
FROM
table1
WHERE
unixTimestamp < 1266428321
GROUP
BY
id
UNION ALL
SELECT
id
,COUNT(*)
,3
FROM
table2
WHERE
unixTimestamp < 1266428321
GROUP
BY
id) t
GROUP
BY
t.id
HAVING
MIN(t.select_num) = 1
AND
MAX(t.select_num) = 4
AND
AVG(t.select_num) BETWEEN 2 AND 3```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•