Query from multiple tables

[Joe Makowiec]

MySQL 4.14 on FC4

I have a database for a soccer team; simplified:

Roster
- serial (bigint)
- name (varchar)
goals
- serial (bigint)
- gameNumber (bigint) // for linking to a schedule
- playerSerial (bigint) // for linking to Roster
assists
- serial (bigint)
- gameNumber (bigint) // for linking to a schedule
- playerSerial (bigint) // for linking to Roster

I can get a count of goals (or assists) separately, thus:

SELECT COUNT(goals.serial) AS myGoals, Roster.name
FROM goals LEFT JOIN Roster ON goals.playerSerial = Roster.serial
GROUP BY Roster.name
ORDER BY myGoals DESC, Roster.name

How can I get both goals and assists (and, presumably, points = goals +
assists) in the same query?

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe

[Markus Popp]

You can do this with a UNION join, but it's better design to store both in
the same table and use another column to separate goals and assists, like
this:

serial
gameNumber
goal_assist enum('G', 'A') // G = goal, A=assist
playerSerial

BTW - do you really need a bigint? That seems to me like a waste of storage,
unless you really have to store values exceeding 4 billions (when they're
unsigned).

Markus

[Joe Makowiec]

On 15 Oct 2005 in mailing.database.mysql, Markus Popp wrote:

You can do this with a UNION join, but it's better design to store
both in the same table and use another column to separate goals and
assists, like this:

serial
gameNumber
goal_assist enum('G', 'A') // G = goal, A=assist
playerSerial
I was wondering about that. Where do I get the count? Otherwise, I'd
add two fields goal/assist (tinyints!), and do a sum rather than a count.
BTW - do you really need a bigint? That seems to me like a waste of
storage, unless you really have to store values exceeding 4 billions
(when they're unsigned).

Don't need it, but I think it was the default. Given the price of
storage, a few bytes here and there shouldn't make that much
difference...

Anyway, thanks for the response.

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe

[Markus Popp]

You can count the goals and assists very easily:

select count(*) from table where playerSerial=5 and goal_assist='G';

gives you the goals from player 5,

select count(*) from table where playerSerial=7 and goal_assist='A';

gives you the assists from player 7 or

select count(*) from table where playerSerial=3;

gives you goals and assists from player 3 ;-).

Markus