MySQL Forums
Forum List  »  Views

Create view from two tables
Posted by: home run
Date: August 16, 2010 12:11AM

Hi,
Guys how to create view from two tables to display daily, monthly and yearly summary. I want to create view with following columns but can't work out the join query. Branchcode is the common in two tables. Please help.

select id, branchcode, monthyear, sum(total) as Received, sum(totalpaid) as Paid, sum(total-totalpaid) as Balance from deposit,expense where branchcode = 'x' and creationdate >= 'commondate' and invoicedate >= 'commondate' group by monthyear;

table deposit:
id
branchcode
creationdate
monthyear
total

select id, branchcode, creationdate, sum(total) from deposit where branchcode = 'x' and creationdate >= 'somedate' group by monthyear;

table expense:
id
branchcode
invoicedate
monthyear
totalpaid

select id, branchcode, invoicedate, sum(totalpaid) from expense where branchcode = 'x' and creationdate >= 'somedate' group by monthyear;



Edited 4 time(s). Last edit at 08/16/2010 12:23AM by home run.

Options: ReplyQuote


Subject
Views
Written By
Posted
Create view from two tables
5353
August 16, 2010 12:11AM
1829
August 16, 2010 04:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.