# php - SQL tournament table with multiple poules

I am creating a website for an tournament. Somewhere on the web i found a sql statement that takes 2 tables, games and teams, and outputs the tournamentstandings.

Below is the team-tabel:

```
id tname poule
1 ZF Eger heren1A
2 Pro Recco heren1A
3 Sintez Kazan heren1A
4 Szolnoki VSE heren1A
5 Sintez Kazan 2 heren1B
6 Szolnoki VSE 2 heren1B
```

And below is the game-tabel:

```
id date hteam ateam hscore ascore gamefield poule played
1 2008-01-01 20:00:00 1 2 0 0 Veld 1 heren1A 0
2 2008-01-01 20:00:00 3 4 10 8 Veld 2 heren1A 1
```

The code below does an sql-query on the database and outputs the league standings.

```
$pouleA = 'heren1A';
$pouleB = 'heren1B';
$query = $mysqli->prepare('SELECT poule AS Poule, tname AS Team, Sum(WG) AS WG,Sum(W) AS W,Sum(G) AS G,Sum(V) AS V, SUM(DV) as DV,SUM(DT) AS DT,SUM(S) AS S,SUM(P) AS P FROM( SELECT hteam Team, 1 WG, IF(hscore > ascore,1,0) W, IF(hscore = ascore,1,0) G, IF(hscore < ascore,1,0) V, hscore DV, ascore DT, hscore-ascore S, CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END P FROM games WHERE played = 1 AND poule = ? OR poule = ? UNION ALL SELECT ateam, 1, IF(hscore < ascore,1,0), IF(hscore = ascore,1,0), IF(hscore > ascore,1,0), ascore, hscore, ascore-hscore S, CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END FROM games WHERE poule = ? OR poule = ?) as tot JOIN teams t ON tot.Team=t.id GROUP BY Team ORDER BY SUM(P) DESC, s DESC');
$query->bind_param('ssss', $pouleA, $pouleB, $pouleA, $pouleB);
```

Problem with this query is that it always counts each match, no matter if it's played or not. How can i use the played-column from the games-tabel in my query so that it only get's the played matches?

*EDIT:

If i change my query to:

```
SELECT poule AS Poule, tname AS Team, Sum(WG) AS WG,Sum(W) AS W,Sum(G) AS G,Sum(V) AS V, SUM(DV) as DV,SUM(DT) AS DT,SUM(S) AS S,SUM(P) AS P FROM( SELECT hteam Team, IF(played = 1,1,0) WG, IF(hscore > ascore,1,0) W, IF(hscore = ascore,1,0) G, IF(hscore < ascore,1,0) V, hscore DV, ascore DT, hscore-ascore S, CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END P FROM games WHERE played = 1 AND poule = ? OR played = 1 AND poule = ? UNION ALL SELECT ateam, 1, IF(hscore < ascore,1,0), IF(hscore = ascore,1,0), IF(hscore > ascore,1,0), ascore, hscore, ascore-hscore S, CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END FROM games WHERE played = 1 AND poule = ? OR played = 1 AND poule = ?) as tot JOIN teams t ON tot.Team=t.id GROUP BY Team ORDER BY SUM(P) DESC, s DESC
```

The teams without played matches aren't selected, but i still want them to be selected cause i need to show the complete poule, even if no matches are played.

## Answer

## Solution:

Try this:

Close to the end you use

`FROM games WHERE poule = ? or poule = ?`

. I just made it say`FROM games WHERE played = 1 AND poule = ? OR poule = ?`

instead.