aidaghazaryan aidaghazaryan - 22 days ago 6
MySQL Question

find unique minimum and maximum dates from 4 tables - php

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

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;