Knepig SQL-fråga sökes...

Hej.

Jag behöver lite hjälp med en SQL-fråga som jag inte kan få ihop.
Jag vill få ut lite statistik från min DB med fotbollsresultat.

Min DB i Access:

MATCH
-id
-resultat

MEMBERS
-id
-first_name
-last_name

GOALS
-id
-match_id
-goal_member_id
-assist_member_id
-minute

YELLOW
-id
-match_id
-member_id
-minute

RED
-id
-match_id
-member_id
-minute

Jag försöker få ut antal mål och assist för alla spelare som har gjort eller assist.
I en separat fråga vill jag ha ut antal kort (gula och röda) för varje spelare.

Håller på att bli galen, e kass på svåra JOIN frågor.

Det där kan väl inte stämma?
Pekar alla id på Match! får du problem nästa gång.
Rött eller gult borde väl vara ett värde lika som minuter?
Händelser är ju Mål, gult eller rött kort.

Du får nog börja med att designa om din databas så att relationerna stämmer.
Om id i Members är matchid så får du problem. Låt members ha en id kolumn och så lägger du till en matchplayers kolumn där du har matchid och lagid. På så sätt får du en koppling till vilka spelare som är med i en match. Du skulle även behöva ha en tabell för lag och en kolumn i members för lagid.
Så här skulle jag designa.

MATCH
- matchid
- hometeam
- awayteam

Målen får två tabeller så att det kan vara flera som får assists.

Goals
- goalid
- matchid
- type (1=home, 2=away)
 
GoalPoints
- pointid
- goalid
- memberid
- minute
- type (1=goal, 2=assist)

MEMBERS
-memberid
-first_name
-last_name

Cards
- cardid
- matchid
- cardtype (1=yellow, 2=red)
- member_id
- minute

Teams
- teamid
- teamname

TeamPlayers
- tpid
- teamid
- memberid

Man skulle även kunna lägga ihop goals, goalpoints och cards till en Events tabell (eventid, matchid, eventtype, memberid, minute) där eventtype ska vara mål, assist, rött, gult, skada, m.m. När någon gör mål med assist så läggs två rader in.

I en sådan datamodell är det väldigt lätt att få ut all information med några enkla JOIN.

Förlåt, glömde match_id.

PDahlen:
En sak som du har rätt i är att det vore bra med en separat tabell för assist, hade så först men hade svårt att få ut den informationen jag behövde.

PDahlen,
det där med Events kankse kan vara nått. Då kankse man kan få in mål, assist, kort,byten mm i en tabell.

Kanske så här:

Match
- id
- result

Events
- id
- match_id
- minute

EventsInfo
- id
- event_id
- event_type
- member_id
- event_msg (kan vara bra för resultat mm)

Tänkte att det kan vara bra med EventsdInfo för ett Event kan ha flera members, tex assist eller byten.
Men jag har lite svårt att få ihop SQL-frågan.:OO

Nu börjar du få ihop din databas korrekt.
Ett exempel för att få ut data med JOIN.

 SELECT m.id, e.id, e.minute, ei.event_type, ei.member_id, ei.event_msg
 FROM Match e JOIN ( Events e JOIN EventsInfo ei ON e.id = ei.event_id ) ON e.m.id = e.match_id
 ORDER BY e.minute ASC

Skulle t.ex få ut alla events i en match i minutordning.
Du kanske ser hur det funkar nu?

Jag skulle nog rekommendera en koppling mellan en assist och ett mål också, annars känns det lite ryckt ur luften.

OveRRidE
Hur menar du? Jag tänkte att om två EventsInfo har samma event_id men event_type är 1 och 2 så är 1=mål och 2=assist.

Men då vill man få ut namnen på både i samma record...

Jaan, det var precis så jag menade.
Override, om man använder Eventstabellen så har man ingen Goals tabell. Goals, Cards, GoalPoints flyter ihop till Events.

Man skulle även kunna lägga ihop goals, goalpoints och cards till en Events tabell (eventid, matchid, eventtype, memberid, minute) där eventtype ska vara mål, assist, rött, gult, skada, m.m. När någon gör mål med assist så läggs två rader in.

Då kan den även användas till utvisningar och skador.
Ett mål läggs då in på följande sätt:
1. Den som gör målet läggs in i Events med typid för mål.
2. Den eller de som får assist läggs in i Events med typid för assist.

Nu kom jag faktiskt på en sak.
Min tanke var ju att kopplingen mellan ett mål och en assist låg i att båda skett i samma matchminut. Men det är ju inte omöjligt att två mål görs i samma matchminut. T.ex. första målet görs efter 23 minuter och 2 sekunder. Sedan görs det ett mål till efter 23 minuter och 56 sekunder. Båda målen hamnar i den 23:e matchminuten.
För att lösa problemet får man antingen lägga in både minuter och sekunder eller så får man på nåt sätt fixa till en koppling, t.ex. med ParentID som pekar mot ett annat Event. Då skulle ett mål t.ex. få ID 54 och ParentID 0. Målets assist skulle få ID 55 och ParentID 54.