Problem with a query

  • Thread starter Thread starter Aleksei
  • Start date Start date
A

Aleksei

Hi!
I have a db of the hotel. Hotel can provide different services, and the
client can use different services (1 and more). Each service has its price.
So i have the following tables:
1) ID
Client
ServiceName
Date
2) ServiceID
ServiceName
Price
I need a query that will show client and total cost of services he used - is
it possible?

Thanks in avance for the answer!
Sveta
 
Aleksei said:
I have a db of the hotel. Hotel can provide different services, and the
client can use different services (1 and more). Each service has its price.
So i have the following tables:
1) ID
Client
ServiceName
Date
2) ServiceID
ServiceName
Price
I need a query that will show client and total cost of services he used - is
it possible?
Click to expand...


I think this will answer the question as asked:

SELECT tbl1.Client, Sum(tbl2.Price) As TotalPrice
FROM tbl1 LEFT JOIN tbl2
ON tbl1.ServiceName = tbl2.ServiceName
GROUP BY tbl1.Client

But that is not an adequate table structure. You have a
classic Many to Many relationship, each client can use many
services and each service can be provided to many clients.
This requires three tables:

Clients:
ClientID
ClientName
ClientAddress
...

Services:
ServiceID
ServiceName
Price
...

Purchases:
ClientID
ServiceID
SellDate
SellPrice
...

Your question can then be answered by:

SELECT Clients.ClientID,
First(Clients.ClientName) As Client
Sum(Purchases.SellPrice) As TotalPrice
FROM Clients LEFT JOIN Purchases
ON Clients.ClientID = Purchases.ClientID
GROUP BY Clients.ClientID

Note that the SellPrice is copied from Services.Price at the
time of the sale. This allows you to maintain the original
selling price even if the service price is changed next
year.
 
Back
Top