Getting count(*) value from multiple tables

I found a solution to my query in this site. I need to get the count(*) value from multiple tables Select count(*) from multiple tables

My other problem is to get the DATE values of those multiple tables that returns > 1 row count. I have to compare the dates and get the latest. I have to query from 12 tables. Assuming I got > 1 value for tables 1, 2, 3, 4, 5, I will need to compare their DATE values. Sample code:

SELECT(SELECT COUNT(*) FROM   table1) AS count1,
(SELECT COUNT(*) FROM   table2) AS count2,
(SELECT COUNT(*) FROM   table3) AS count3 FROM dual

count1 count2 count3
  3       2     2

I need to select the MOST RECENT date in these tables.

Select date from table1 order by date desc
Select date from table2 order by date desc
Select date from table3 order by date desc

How am I supposed to do that without table scanning?

EDIT: Okay. The instructions said "Get matching records from tables 1-12 using the id as the key".开发者_运维技巧 If there are no records, display "No record found" Else: get the record with the latest date by comparing data_encoded from all tables.

It's pretty hard to tell what you're after, but here's a guess:

SELECT
    (SELECT MAX(date) FROM table1) AS latest1,
    (SELECT MAX(date) FROM table2) AS latest2,
    (SELECT MAX(date) FROM table3) AS latest3,
    -- etc
FROM dual;

You can avoid table scans by having indexes on date, in which case the optimizer should do index-only scans (very fast).

have you try to group them and order it and select the first date

select ID, Date, count(date) from table1 t1 inner join table2 t2 where t1.id = t2.id inner join table3 t3 where t1.id = t3.id etc.. group by date order by date desc

something long that line