Getting count(*) value from multiple tables

Question

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.

I can't tell what you're after. Some example output would be helpful. — Brad Mace, Jun 23 '11 at 05:42
Yes, I was looking for the most recent date for 12 tables. I will also try the code below given by Bohemian — Camille, Jun 23 '11 at 06:02
@OMG Ponies. No, 12 tables with different date column values. — Camille, Jun 23 '11 at 06:09

score 3 · Answer 1 · answered Jun 23 '11 at 05:52

3

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).

answered Jun 23 '11 at 05:52

Bohemian

412,405
93
575
722

Got this working. Now I have to get the MAX() date of the results that has an output. thanks so much. – Camille Jun 28 '11 at 01:51

score 0 · Answer 2 · answered Jun 24 '11 at 05:28

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

Getting count(*) value from multiple tables

2 Answers2