tags:

views:

49

answers:

1
Q: 

howto make such a mysql query?

hi there i want to design a football result table from a existing database structure.

TEAM(id, name)
GAME(id, team1id, team2id, goalsTeam1, goalsTeam2)

the result table should look like:

name games points
name games points
name games points

(the points can be get by looking who hase make more goles (win=3,draw=1,loose=0))

i dont know how to make the query can somebody help me?

A: 

Can't you do that programmatically? I would join the two tables, so that all relevant columns are in the result set, iterate the result set, calculate and store the result in something like a hashtable or so.

gemue  2010-09-30 07:17:59
No because i want to make a view of it
latte_makao  2010-09-30 08:23:11
I doubt that there is a solution with a single sql statement. The aggregation and calculation with the external knowledge (win=3, draw=1, loose=0 and win=[goalsTeam1-goalsTeam2) > 0 and so on) is quite complex and you will also run into grouping problems, because the same team will probably occur in both team columns. What about triggering a stored procedure triggered on inserting a game in the game table that does the calculation programmatically and writes an additional result table?
gemue  2010-09-30 11:14:39

related questions

Updating a table within a select statement
How do I make an expression inside a select statement without it being returned as column?
Updating multiple rows with a value calculated from another column
How to perform grouped ranking in MySQL
MySql : UNION is not getting executed when executed as a view
MySql Union not getting executed in a view
Removing an Appended " In front of Every Column
Best way to prevent a value from going negative in mysql
Copy MySQL structure across servers via PHP
How can I query from 2 dates ranges (mysql)
MySQL function to insert record if update fails?
Rather difficult mySQL problem ...
PHP / MySql Verbosity VS over the wire
SQL to search and replace in mySQL
How to Avoid Throwing An Error When SQL Select Returns Nothing
MySQL permissions issue - should be non-issue
mysql stored procedure question
Mysql efficient "select id else insert" query
MySQL - A difficult INSERT...SELECT on the same table [MySQL 5.051]
Can I concatenate multiple MySQL rows into one field?
search question
Checking if mysql_query returned anything or not
How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table?
MySQL Query: LIMITing a JOIN
Multiple Updates in MySQL