Best way to extract 2 columns from multiple tables

[denvious]

Hello all,

I have a database which has 10 tables. Each table has an id, a title and a date.

Now i'm having difficulty finding the best way to extract the latest date from each unique table, while also doing a count(*) attribute to show how many rows I have in that table.

Here is an example -

I used the code tag to make it easier to see,

Table A has 3 values.
+----+----------------+------------+
| ID |  Title         | date       | 
+----|----------------+------------+
| 1  |  First Row     | 2011-06-15 |  
| 2  |  Second Row    | 2011-06-14 | 
| 3  |  Third Row     | 2011-06-13 | 
+----+----------------+------------+

Table B has 2 values.
+----+----------------+------------+
| ID |  Title         | date       | 
+----|----------------+------------+
| 20 |  First Row     | 2011-05-15 |  
| 30 |  Second Row    | 2011-03-14 |  
+----+----------------+------------+

Is it even possible for me to attain this? -

Latest date of the unique table is shown, only has 1 row per table.

+----+----------------+------------+----------+
| ID |  Title         | date       | count(*) |
+----|----------------+------------+----------+
| 1  |  First Row     | 2011-06-15 |        3 |
| 20 |  First Row     | 2011-05-15 |        2 |
+----+----------------+------------+----------+

If its possible, what is the best method of coding the sql query? A nested query?

** I am using php to connect to the db as well as format the output.

Thanks

[smantscheff]

Do a UNION query which combines all tables together with a table ID.
Make it a view.
Then query this view for ID, Title, Max(Date), Count(*) GROUP BY tableID.

[denvious]

Thanks for replying smantscheff. One little thing remains.

The tables all have different "names" for their IDs, how do i group them by their unique id's?

create view v as SELECT * from table1 UNION SELECT * from table2;
select * from v;

This gets (as an example)

+-ID-+--Title--+----date----+
| 1  | First   | 2011-06-05 |   <-- First 2 rows of the first table.
| 2  | Second  | 2011-06-04 |
| 10 | Third   | 2011-06-03 |   <-- Union join of the 2nd table
+----+---------+------------+       However the ID column is not named id.

Would it be easier for me to rename all table "ids" to be the same? so the group by would then work?

Thanks,

[pratik_garg]

you can do one thing...

just take union on all table having max(date) condition in every sub union query..

so that by this different different sub query you will get latest date row/rows.

now about count(*) ...
this you have to do in different sub query...

i can write a query also for this ... but please try this from your side..

[denvious]

I think i've got it.

select title, MAX(date),count(*) from table1 
UNION 
select title, MAX(date),count(*) from table2;
+-------------------------------+------------+----------+
| title                         | MAX(date)  | count(*) |
+-------------------------------+------------+----------+
| First from table 1            | 2011-06-15 |        2 |
| Second from table 2           | 2011-06-14 |        1 |
+-------------------------------+------------+----------+

Thanks pratik_garg and smantscheff.

[denvious]

select title, MAX(date),count(*) from table1 
UNION ALL
select title, MAX(date),count(*) from table2;

Slight correction to show ALL data, UNION doesn't pull all data from the database.