table join problem

madhavr

i have a problem

i have three tables ....

landlord_statement
staff_statement
supplier_statement

each one shares a very similar structure

they have ID's -- statementdate
and other things chequedateto, chequedatefrom and so on

i want to combine all three tables into one all_statements table
with just statementdate, chequedateto, chequeedatefrom with repeating these cols

i know that they can be joined since the IDs never interfere with each other

i need this all_statements data to be sent to excel spreadsheet

any ideas??

barand

3 separate queries

INSERT INTO all_statements
SELECT statementdate, chequedatefrom, chequedateto
FROM tablename

madhavr

i understand what your saying but i have some questions

since my ultimate goal is to get this all statements table into excel via ms query & mysql odbc drivers. this is easy enough if i have everything exported into the all statements table.

this all statements table will need to be updated on a monthly basis so i want to know that if i do inserts of already existing data will mysql overwite these records??? or is there a way just to insert only the new records since last month?

im thinking its too difficult to have mysql automatically update the all statements table so maybe i need to introduce a section in my system where the authorised user can update the all statements table before going into excel???

let me know what you think
thanks very much

nnichols

You can just set a unique key on the table so that statements already entered are ignored.

INSERT IGNORE INTO all_statements
SELECT statementdate, chequedatefrom, chequedateto
FROM tablename
WHERE statementdate > DATE_SUB(NOW(), INTERVAL 35 DAY);

As you have a date column in the statements tables you should also have a WHERE clause limiting the INSERTs to rows entered in the last 35 days or so to avoid unnecessary overhead.

Hope this helps