database query - can't get this to work :(
 

simplistic view

3 tables
--------

booking accomodation activity
------- ------------- --------

id id id
num_people status status
total total
deposit deposit

where the id is common across all tables
status = paid or invoiced
debt = total-deposit

so, if i want to know the debt for each booking (i.e. where status = invoiced) how do i go about it. i've tried union queries, nested sql, etc but can't get a solution.

cheers
steven

Sorry if I am misunderstanding but it looks like you can just get the information from the one table, accomodation, unless the tabbing is really off the wall.

select id, (total-deposit)
from accomodation
where status = "invoiced"

If the tables are different from what they look like let me know and I'll have another bash.


Mark

Mark

sorry, the accomodation element may have a status of paid, but perhaps the activity is invoiced but not paid, so the status's and amounts outstanding can differ.

dont know if this will definately work as ive tried it blind...

select booking.id, isnull(accomodation.total,0) + isnull(activity.total,0) as debt
from booking
join accomodation on
(booking.id=accomodation.id and accomodation.status='invoiced')
join activity on
(booking.id=activity.id and activity.status='invoiced')

quickly went through it and came up with this hopefully this is right


select booking.id,
(isnull(accomodation.total,0) - isnull(accomodation.deposit,0)) + (isnull(activity.total,0) - isnull(activity.deposit,0)) as debt
from booking, accomodation, activity where booking.id = accomodation.id and booking.id = activity.id and
(accomodation.status = 'invoiced' or activity.status = 'invoiced')