I've 2 tables.

TABLE_1 ( id, date, column_1 )
TABLE_2 ( id, date, column_1, column_2 )

id and date are typical for tables. I wish to create a view mixing both of these.

VIEW_LATEST ( index, table_name, id, date)

Within the view I want a catalog to recognize a specific row individually (not really a must). There, I'll possess the table title to recognize the truth that where the particulars of the particular row (id, date) was taken. And, I have to obtain the information of the particular period only (eg. a week ago).

Eg.

TABLE_1 ([ 1, 2012-01-27 ], [2, 2012-02-01 ])
TABLE_2 ([ 1, 2012-01-20 ], [ 2, 2012-01-31 ])
VIEW_LATEST ([ tbl_1, 2, 2012-02-01 ], [ tbl_2, 2, 2012-01-31 ], [ tbl_1, 1, 2012-01-27])

A week ago - 2012-01-26 to 2012-02-01, So TABLE_2 first record isn't came back within the view. Purchased by date.

I have to just do as pointed out above. I sought out an answer something similar to this and never found.

Any means to fix achieve above scenario is extremely appreciated. Further, expect to create a separate table of format VIEW_LATEST to place data when placing data to TABLE_1, TABLE_2 to obtain the latest when needed. So, that'll be one solution if you can't really implement the above mentioned scenario.

You can UNION the tables together.

For MySQL (no subquery permitted):

(SELECT 'Table_1' as tablename, id, date FROM Table_1
WHERE date BETWEEN TO_DATE('20120126', 'YYYYMMDD') AND TO_DATE('20120201', 'YYYYMMDD'))
UNION 
(SELECT 'Table_2' as tablename, id, date FROM Table_2
WHERE date BETWEEN TO_DATE('20120126', 'YYYYMMDD') AND TO_DATE('20120201', 'YYYYMMDD'))
ORDER BY tablename, id, date

Result set could be:

tablename    id    date
---------    --    -----------
Table_1       1    2012-01-27
Table_2       2    2012-01-31
Table_1       2    2012-02-01
SELECT src_table, id, the_date
  FROM (SELECT 'TABLE_1' AS src_table, id, the_date
          FROM table_1
          WHERE the_date BETWEEN TO_DATE('2012-01-26', 'YYYY-MM-DD')
                             AND TO_DATE('2012-02-01', 'YYYY-MM-DD')
         UNION
         SELECT 'TABLE_2' AS src_table, id, the_date
           FROM table_2
           WHERE the_date BETWEEN TO_DATE('2012-01-26', 'YYYY-MM-DD')
                              AND TO_DATE('2012-02-01', 'YYYY-MM-DD')
        )
  ORDER BY the_date DESC, id, src_table

Replace your dates with SYSDATE-7 or whatever is suitable

In mysql subqueries aren't permitted within the CREATE VIEW. So, I produced 2 sights VIEW_LATEST_SUB and VIEW_LATEST to get this done.

CREATE VIEW VIEW_LATEST_SUB AS
SELECT 'table_1' AS src_table, id, DATE FROM table_1 
WHERE DATE BETWEEN "2012-01-26" AND "2012-02-01"
UNION
SELECT 'table_2' AS src_table, id, DATE FROM table_2 
WHERE DATE BETWEEN "2012-01-26" AND "2012-02-01";


CREATE VIEW VIEW_LATEST AS
(SELECT * FROM view_latest_sub ORDER BY DATE DESC);

So, I'm able to use VIEW_LATEST to satisfy my requirement. Anyway there can be a different way to join these 2 sights into one. If somebody understands how to do this, help me.

PS : There happens an issue, if VIEW_LATEST_SUB, SELECT totally included in parenthesis. This can be because of utilization of UNION.