Create View
HejJeg har en db med 3 tabeller.
<b>Team</b>
idteam
Name
<b>Match</b>
idMatch
Date
idTeamA
idTeamB
<b>TeamMatch</b>
idTeamMatch
idMatch
idTeam
Score
Point
Jeg er ved at lave et view som viser stilling... Jeg har følgende:
SELECT DISTINCT Team.idTeam,
Team.Name,
(
SELECT COUNT(*)
FROM TeamMatch
WHERE Point = 2 AND
TeamMatch.idTeam = Team.idTeam
) AS Won,
(
SELECT COUNT(*)
FROM TeamMatch
WHERE Point = 1 AND
TeamMatch.idTeam = Team.idTeam
) AS Draw,
(
SELECT COUNT(*)
FROM TeamMatch
WHERE Point = 0 AND
TeamMatch.idTeam = Team.idTeam
) AS Lost,
SUM(TeamMatch.Score) AS ScorePlus,
SUM(TeamMatch.Score) AS ScoreMinus,
SUM(TeamMatch.Point) AS Point
FROM Team LEFT OUTER JOIN
TeamMatch ON
Team.idTeam = TeamMatch.idTeam
GROUP BY
Team.Name,
Team.idTeam
Problemet er ScoreMinus. Den er helt forkert. jeg kan ikke få den til at vise hvor mange må der er blevet scoret IMOD et hold. Jeg ved at jeg skal lave en "nested" select, men kan ikke rigtig få den til det..
På forhånd tak
/Weje