I have a MySQL 5.1.44 Database that I'm using with PHP 5.3.1.
SELECT MAX(t1.tstamp) AS tstamp1, MAX(t2.tstamp) AS tstamp2 FROM table1 t1, table2 t2;
This works perfectly well if both tables contain content, but it returns NULL for both (!) even if only one of the tables is empty. Why does it not simply return the correct value for table1 and null for table2?
Use two queries - cross join with an empty table will result zero rows
Something like this should do the work
SELECT
(SELECT MAX(tstamp) FROM table1) AS tstamp1,
(SELECT MAX(tstamp) FROM table2) AS tstamp2;
left join and find the MAX of table valuesIf there is no relation between the tables use separate query and find the max values
Because you are cross joining the 2 tables. The (intermediate) result table has (0 x N =) 0 rows.
Your query is:
SELECT
MAX(t1.tstamp) AS tstamp1,
MAX(t2.tstamp) AS tstamp2
FROM
table1 t1 CROSS JOIN table2 t2 ;
You could work around the problem by running 2 subqueries and then UNION them, which would return either 0, 1 or 2 rows:
SELECT
'table1' AS tablename, MAX(tstamp) AS tstamp
FROM
table1
UNION ALL
SELECT
'table2' AS tablename, MAX(tstamp) AS tstamp
FROM
table2 ;
or if you want strictly 1 row and NULL result when a table has no rows, you can do this:
SELECT
MAX(t1.tstamp) AS tstamp1,
MAX(t2.tstamp) AS tstamp2
FROM
(SELECT 1) AS dummy
LEFT JOIN table1 t1 ON TRUE
LEFT JOIN table2 t2 ON TRUE ;
or this:
SELECT
(SELECT MAX(tstamp) FROM table1) AS tstamp1,
(SELECT MAX(tstamp) FROM table2) AS tstamp2
FROM
dual ;
The behavior you observe is likely due to one of the tables being empty. (If one of the tables is empty, then the JOIN operation won't return any rows.)
Here's a query which is one way to workaround the issue:
SELECT d1.tstamp1, d2.tstamp2
FROM ( SELECT MAX(t1.tstamp) AS tstamp1 FROM t1 ) d1
CROSS
JOIN ( SELECT MAX(t2.tstamp) AS tstamp2 FROM t2 ) d2