JOINS and NESTED SELECT? 1

[worldwise]

I have these two tables:

CHECKS [1:M] RECEIPTS
checkNumber (PK) receiptID (PK - autonumber)
checkAmount checkNumber (FK)
checkDate receiptAmount


I would like a single table with the following 3 columns:

Checks.checkNumber, Checks.checkAmount, Sum(Receipts.receiptAmount)



I have this so far:

SELECT checkNumber, checkAmount FROM CHECKS LEFT JOIN ON
(SELECT Receipts.checkNumber, Sum(receiptAmount) FROM
RECEIPTS GROUP BY Receipts.checkNumber) WHERE Checks.checkNumber = XXX.checkNumber.

I think my problem is that I do not know how to identify the 2nd table that I created. Therefore I have XXX.checkNumber.

Any suggestions/tips/help?

Thanks!~

 

[PHV]

The correct syntax is:
SELECT C.checkNumber, C.checkAmount, R.TotalReceipt
FROM Checks AS C LEFT JOIN (
SELECT checkNumber, Sum(receiptAmount) AS TotalReceipt
FROM Receipts GROUP BY checkNumber
) AS R ON C.checkNumber = R.checkNumber;

A simpler way:
SELECT C.checkNumber, C.checkAmount, Sum(R.receiptAmount) AS TotalReceipt
FROM Checks AS C LEFT JOIN Receipts AS R ON C.checkNumber = R.checkNumber
GROUP BY C.checkNumber, C.checkAmount;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[worldwise]

Hah, ok then!

It just seems to me like it takes more time the 2nd way because I keep imagining a nested loop where the computer has to group by column1 and then go back again to group by column2.

In my post I only have 2 columns but I really want to have 5 columns and that just seems like a nested loop of 5 deep.

But it seems like your the expert so I'll go ahead with your solution from the other post PHV.

Thanks again~~

 

[lespaul]

anytime you add a field to your SELECT clause in a query that uses an aggregate function (SUM, COUNT, AVG) you have to also include that field in the GROUP BY clause.

Those are the rules!

Search the forum for "aggregate query" and you will find a lot of examples and explanations.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[worldwise]

lespaul,

Yeah, thanks. I do understand that from a syntax aspect, but it just seems like a "workaround" because there really is nothing to group by. I mean, we are grouping by the primary key field in one instance, but there is nothing to group since its the primary key.

I guess I'm just having trouble with the idea of it, not the syntax of it.

Thanks for your input though~

-kevin