[RESOLVED] is there any query string that can do this?

[sphericalx]

This are the tables examples.

ServicesAndProductsTable
productID, productDescription, type, productCategory
1000, product1, product, skincare
1001, product2, service, facial
1002, product3, product, skincare
1003, product4, service, manicure

ReceiptSummaryTable
ReceiptID, clientID, totalAmt, employeeName
11000, 100, $10, James
11001, 110, $5, James
11002, 150, $15, Sandy

ReceiptRowsTable
ReceiptID, productID, type, amt
11000, 1000, product, $5
11000, 1003, service, $5
11001, 1002, product, $5
11002, 1003, service, $5
11002, 1001, service, $10

==============================================
Expected output:

employee ID, employee Name, productTotal, facialTotal, manicureTotal
1, James, $10, $0, $5
6, Sandy, $0, $10, $5

is there a query which can do this?
productTotal is calculated from the ReceiptSummaryTable where type = product

As for facialTotal and manicureTotal will be slightly complicated.
facialTotal is calculated from ReceiptSummaryTable where type = product AND category = facial in ServicesAndProductsTable.
likewise for manicureTotal.

anyway, i'm using sql server 2000.