January 30th, 2004, 11:31 AM
Need help getting value returned by SubQuery
I'm doing some work on a database that keeps track of Chicken Catchers and their daily catching totals. There is a couple of tables being used the first is tblCatcher, this holds info about the catchers and how they did on the catch. tblDaily holds the farm information where they caught that day. tblRate is a list of different rates for each type of catch they could perform. Now the following is the SQL that is being called from the ASP page the user will enter two dates and be returned with the corresponding results. The problem is I want to reference a one to many relationship where a field tblCatcher.Trailers will do a SUM of the total weight on the tblTrailers.TotalWeight. The trailers field is a ID field unike to the record; however, a catcher can work on multiple trailers so the trailers field on the tblCatcher.Trailers would look like "223, 224, 225"
SELECT SUM(TotalWeight) AS myTotal FROM tblCatcher WHERE RecordID = ([@RecordID]) AND Trailers LIKE ([@Trailers])
SELECT (exec sq_TotalWeight [tblCatcher.RecordID], "[tblCatcher.Trailers]") AS TWeight, tblCatcher.*, tblDaily.rateID, tblRate.Value, tblRate.Type, tblRate.SupervisorRate, tblDaily.Farm
FROM tblCatcher INNER JOIN (tblDaily INNER JOIN tblRate ON tblDaily.rateID = tblRate.rateID) ON tblCatcher.RecordID = tblDaily.ID
WHERE ((([tblCatcher.CatchDate]) Between ([@StartDate]) And ([@EndDate])))
ORDER BY tblCatcher.LastName, tblCatcher.FirstName, tblCatcher.FileNumber;
If you need to to clarify any of these SQL statements please email me.