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).
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 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
SELECT totally included in parenthesis. This can be because of utilization of