zeos sql query on firebird

[chunkiebone]

hello i have 3 tables, namely masterlist and daily_payments

masterlist has columns named client, address and balance

daily_payments has columns named date, payment, client and balance.


i would like the data on the balance same on both tables and when updated on either table, i will update on the other one.

i have though of an idea to make another table for balance but cant figure out how to make a sql statement that will display data from more than 1 table.

or do i need to setup a table relation for each of them?

[marsupilami]

Hello chunkiebone,

I am not sure what you want to do but keeping the same data twice in the database is good way to get inconsistent data if you make a mistake.

For querying two or more tables use the join synax. Assuming you have three tables:
masterlist (id, client, address)
daily_payments (date, payment, client)
balances (client, balance)

then you could do something like this:
select * from masterlist m join balances b on (m.id = b.client) join daily_payments p on (m.id = p.client)

Although this most probably is not what you want to do, you can do it

Best regards,

Jan

[chunkiebone]

hello jan,

basically what i want to do is that the column in the masterlist be updated when i insert a payment on the daily collection. because the daily collection would have dates everyday with payments from other clients, while the masterlist will hold the profile of the clients and will just be edited maybe once or every renewal. thanks for the reply you gave me.

best regards,

cedris

[marsupilami]

Hello Chunkiebone,

there are several ways to accomplish what you want. But the most easy way might be to use a stored procedure in Firebird that acts after insertion of a record to the payments table.
For safeguarding the data you also might want to find a way to keep people from messing with the balance in the masterlist or with the ammounts in the payments list. This also might be done with stored procedures and rights and views...
Best regards,

Jan

[chunkiebone]

Hi Jan,

Thank you very much for the information you gave me. I just wonder where I could start. I am a total newbie when it comes to firebird and coding, and I dont even know how to build table relationships in firebird.

btw I'm using flamerobin app to make it easier for me to manage my firebird database.


Thanks.

Best regards,

Cedris