Noob needs a hand with Joins.

[mreish]

I recently took over a project for a local non-prof that holds a run/walk event to raise funds each year.  I'm having a heck of a time coming up with the query to get the report they're asking for.

 

Here's what they'd like to see:

Pack Name: Bob's Pack
     Bob    29.95
     Susie  0
     Total: 29.95

     
Pack Name: Joe's Pack
     Joe    39.90
     Total: 39.90

     
Grand Total: 59.90

 

Here are the tables:

Tables: transactions

transactionID  transactionAmount   transactionReasonCode    walkerID
1              29.95               1                        1
2              19.95               1                        3
3              19.95               1                        3



Table: walkers

walkerID  walkPackID     walkerName
1         1              Bob
2         1              Susie
3         3              Joe



Table: Packs

packID    walkerID       packName
1         1              Bob's Pack
3         3              Joe's Pack

 

And here's my query so far:

SELECT packs.packName, walkers.walkerID 
COUNT (transactions.transactionAmount) AS total 
FROM transactions, packs, walkers 
WHERE transactions.transactionReasonCode=1 
AND walkers.walkerID = transactions.walkerID 
AND packs.packID = walkers.walkPackID 
GROUP BY walkers.walkPackID

 

Obviously the query isn't working but can someone lend me a hand? I've been looking through Paul DuBois book and feel that I'm heading in the right direction but I've run into a wall.

They're using PHP 5 and MySQL 4.

 

Thanks a ton.

[Stefan]

I know I`ve mentioned this before but it help me lot maybe it can help you as well

 

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[fenway]

What is your currect output?