find unique minimum and maximum dates from 4 tables - php

Question

I'm using wordpress and have 4 custom tables. Each of them looks like this:

Table1       Table2        Table3        Table4

date         date          date          date
2016-07-21   2016-11-13    2016-10-19    2016-11-18  
2016-09-16   2016-10-27    2016-11-13    2016-10-25
2016-09-09   2016-09-30    2016-07-15    2016-09-28
2016-11-11   2016-08-19    2016-11-17    2016-10-24

I need to find minimum date of these 4 tables and maximum date of these 4 tables. I tried to do that via below code:

$minViews = $wpdb->get_results("
SELECT MIN(date) as min_date FROM Table1 UNION 
SELECT MIN(date) as min_date FROM Table2 UNION 
SELECT MIN(date) as min_date FROM Table3 UNION 
SELECT MIN(date) as min_date FROM Table4");

$maxViews = $wpdb->get_results("
SELECT MAX(date) as max_date FROM Table1 UNION 
SELECT MAX(date) as max_date FROM Table2 UNION 
SELECT MAX(date) as max_date FROM Table3 UNION 
SELECT MAX(date) as max_date FROM Table4");

But above query returns each table's minimum and maximum date. But I need to find minimum date of all 4 tables and maximum date of all 4 tables.

Answer 1

You can always use a subquery in your FROM clause:

SELECT MIN(date), MAX(date) FROM
(
    SELECT date FROM Table1
    UNION SELECT date FROM Table2
    UNION SELECT date FROM Table3
    UNION SELECT date FROM Table4
) AS sq;