database query - can't get this to work :(
#1
Scooby Regular
Thread Starter
Join Date: Sep 2001
Location: Bangor, Northern Ireland
Posts: 2,033
Likes: 0
Received 0 Likes
on
0 Posts
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
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
#2
Scooby Regular
Join Date: Jun 2004
Location: Bedfordshire
Posts: 528
Likes: 0
Received 0 Likes
on
0 Posts
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
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
#3
Scooby Regular
Thread Starter
Join Date: Sep 2001
Location: Bangor, Northern Ireland
Posts: 2,033
Likes: 0
Received 0 Likes
on
0 Posts
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.
#4
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')
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')
#5
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')
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')
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
38
17 July 2016 10:43 PM