Select data: paginating data from multiple tables with inconsistent dates

Question

I have 8 separate tables. Each table has id, a datetime field and some text.

I am presenting data combined from all tables on a single page in a timeline view, most recent entries are at the top, and entries from each table are mixed in this timeline.

Now the hard part - I need to add pagination to this website, so on each page I want to show exactly 10 days (except for the last page, that may have less than 10). Each table may have variable number of rows.

I've been struggling with this for quite some time but have not yet come up with an elegant solution.

Here's an example (I'll pick only two tables for this example to make it simpler).

tableA

Apr 1 | a1
Apr 5 | a2
Apr 7 | a3

tableB

Apr 2 | b1
Apr 2 | b2
Apr 5 | b3
Apr 6 | b4

Global timeline would look like this

---

Apr 7 a3
Apr 6 b4
Apr 5 a2
Apr 5 b3
Apr 2 b2
Apr 2 b1
Apr 1 a1

And if each page shows only 3 days, I need for it to look like this:

--- p1 ---
Apr 7 a3
Apr 6 b4
Apr 5 a2

--- p2 ---
Apr 5 b3
Apr 2 b2
Apr 2 b1

--- p3 ---
Apr 1 a1

The problem is - I can't figure out a way to query for this data in an elegant way. Here's some live query that I've been messing with:

select date(d.entryTime) entryDate, date(wc.changeTime) wcDate from diary_entry d join water_change wc on d.aquariumId = wc.aquariumId where d.aquariumId = 2 group by entryDate order by entryDate limit 10, 5

so, for one table I have this query: select date(d.entryTime) entryDate from diary_entry d where d.aquariumId = 2 group by entryDate

it yields 13 results entryDate 2012-01-13
2012-01-14
2012-01-25
2012-01-26
2012-01-31
2012-02-04
2012-02-17
2012-02-20
2012-02-25
2012-03-17
2012-03-31
2012-04-01
2012-04-06

and for another: select date(wc.changeTime) changeDate from water_change wc where wc.aquariumId = 2 group by changeTime

it yields 8 results 2012-01-22
2012-01-26
2012-02-17
2012-02-25
2012-03-04
2012-03-10
2012-04-04
2012-04-24

There are three common days between the two 2012-01-26 2012-02-17 2012-02-25

So the query that I need to produce would have to have 13 + 8 - 3 rows = 18 rows

And solution is found! (select date(d.entryTime) activityDate from diary_entry d where d.aquariumId = 1 group by activityDate) union (select date(wc.changeTime) activityDate from water_change wc where wc.aquariumId = 1 group by activityDate order by activityDate) limit 10, 10

Answer 1

Query it using a Union.

SELECT TheDate,
       TheText
  FROM Table_A
 WHERE [your critera]
 UNION
SELECT TheDate,
       TheText
  FROM Table_B
 WHERE [your critera]
...

If you need to only select the distinct ones, you can then wrap this as follows:

Select Distinct
       TheDate,
       TheText
  From 
(
 SELECT TheDate,
       TheText
  FROM Table_A
 WHERE [your critera]
 UNION
SELECT TheDate,
       TheText
  FROM Table_B
 WHERE [your critera]
...
);