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
– Brad Mace, Commented Jun 23, 2011 at 5:42
Yes, I was looking for the most recent date for 12 tables. I will also try the code below given by Bohemian — Camille
– Camille, Commented Jun 23, 2011 at 6:02
@OMG Ponies. No, 12 tables with different date column values. — Camille
– Camille, Commented Jun 23, 2011 at 6:09

Bohemian · Accepted Answer · 2011-06-23 05:52:01Z

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, 2011 at 5:52

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Camille Over a year ago

Got this working. Now I have to get the MAX() date of the results that has an output. thanks so much.

B4ITZUP · Accepted Answer · 2011-06-24 05:28:06Z

0

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

answered Jun 24, 2011 at 5:28

B4ITZUP

213 bronze badges

Collectives™ on Stack Overflow

Getting count(*) value from multiple tables

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related