Calgarypuck Forums - The Unofficial Calgary Flames Fan Community - View Single Post

iggyntangs · 04-08-2011, 01:51 PM

With regards to that last query (about total price for the customer) the part that's really confusing me and making it hard for me to find a total price one confirm_no owes (i.e. the one guest that is linked to that confirm_no) is that I have to add up other things in addition to the base pay, there's also an invoice table (for other rentals during their stay) that I need to add into the total the guest pays just for the room.

So:

create table invoice(
inv_no int,
inv_date datetime,
inv_amount money,
confirm_no int);

alter table invoice
alter column inv_no int
not null;

alter table invoice
add constraint pk_invoice primary key (inv_no);

alter table invoice
add constraint fk2_invoice foreign key (confirm_no) references rental ON DELETE CASCADE;

I need to take the total calculate originally (in your query) and add the inv_amount to that (which is linked through the confirm_no), how would I do that?

Ideas?

04-08-2011, 01:51 PM	#9
iggyntangs Scoring Winger Join Date: Oct 2010 Exp:	With regards to that last query (about total price for the customer) the part that's really confusing me and making it hard for me to find a total price one confirm_no owes (i.e. the one guest that is linked to that confirm_no) is that I have to add up other things in addition to the base pay, there's also an invoice table (for other rentals during their stay) that I need to add into the total the guest pays just for the room. So: create table invoice( inv_no int, inv_date datetime, inv_amount money, confirm_no int); alter table invoice alter column inv_no int not null; alter table invoice add constraint pk_invoice primary key (inv_no); alter table invoice add constraint fk2_invoice foreign key (confirm_no) references rental ON DELETE CASCADE; I need to take the total calculate originally (in your query) and add the inv_amount to that (which is linked through the confirm_no), how would I do that? Ideas?