Mysql SELECT last value from 2 tables, 2 columns (with the same name)

advertisements

Table '2_1_journal'

NumberRenamed
-------------
 1
 2
 3

Table '2_1_paidused'

NumberRenamed
-------------
 1
 2

Want to select last values from each table and get array with the 2 values.

Based on examples, created such code

SELECT 2_1_journal.NumberRenamed, 2_1_paidused.NumberRenamed 

FROM 2_1_journal

LEFT JOIN 2_1_paidused ON 2_1_journal.NumberRenamed = 2_1_paidused.NumberRenamed

ORDER BY NumberRenamed DESC LIMIT 1

$stmt->execute();
$ArrayWithTwoNumbers = $stmt->fetchColumn();

But get error SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'NumberRenamed' in order clause is ambiguous.

If remove ORDER BY NumberRenamed DESC LIMIT 1 get value 1 (first value from 2_1_journal.NumberRenamed).

Please advice what need to correct.

Simple way would be 2 queries with SELECT. But from my knowledge to save resources better to use one query....

Changed to ORDER BY 2_1_journal.NumberRenamed DESC LIMIT 1 and get 123

Changed to

(SELECT NumberRenamed FROM 2_1_journal
UNION
SELECT NumberRenamed FROM 2_1_paidused)

ORDER BY NumberRenamed DESC LIMIT 1

get SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT NumberRenamed FROM 2_1_paidused) ORDER BY NumberRenamed DESC LI' at line 2

Changed to

(SELECT NumberRenamed FROM 2_1_journal )
UNION ALL
(SELECT NumberRenamed FROM 2_1_paidused)

ORDER BY NumberRenamed DESC LIMIT 1

no error, but result is not as expected (get last value from table 2_1_paidused). How to get last value also from table 2_1_journal?

Seems this is solution

(SELECT NumberRenamed as Number1 FROM 2_1_journal ORDER BY Number1 DESC LIMIT 1)
UNION
(SELECT NumberRenamed as Number2 FROM 2_1_paidused ORDER BY Number2 DESC LIMIT 1)

and $stmt->fetchAll();

Get

[0] => Array
    (
        [Number1] => 3
        [0] => 3
    )

[1] => Array
    (
        [Number1] => 2
        [0] => 2
    )

Values are correct (as expected). But why in [0] and [1] there is [Number1]? Expected in [1] [Number2]


Test something like:

select
  (select max(NumberRenamed) from 2_1_journal) max1,
  (select max(NumberRenamed) from 2_1_paidused) max2

It is just a little better than 2 distinct queries, the only difference is it has 1 remote call (network overhead), but using 2 distinct queries results 2 remote calls.