Getting aggregate values from two different tables

[sgietz]

Hello,

To be brief, I have two tables that hold football stats. One table holds passing info, and the other holds rushing info. Each row has a player id.

What I like to do is get the sum of each field for a particular player. Here's what I have:

SELECT
SUM(pass_completions),
SUM(pass_attempts),
SUM(pass_intercepts),
SUM(pass_yards),
SUM(rush_number),
SUM(rush_yards)
FROM passes pa, rushes ru
WHERE pa.player_id = 82
AND ru.player_id = 82

That doesn't work. It gives me wrong numbers. It does work fine when I get the stats from only one table at a time like this:

SELECT
SUM(pass_completions),
SUM(pass_attempts),
SUM(pass_intercepts),
SUM(pass_yards)
FROM pressbox.passes pa
WHERE pa.player_id = 82

How can I pull this off with multiple tables? It would be nice to get this done with just one query.

Thanks!

[sgietz]

Once again, I figured out a workaround. I'm using UDFs to get this working, which actually works like a charm, because I can incorporate that into the initial query, which gets the basic player info. Now I can get all of it in one shot.

Love it! :)