Union query behaviour: Is it supposed to do that? (1 Viewer)

[petehilljnr]

I have a table tbl_Fixtures made up of:

fixture_id
team1_id
team2_id
...
...
...

A team id can appear in either team1 or team2 (depends on how the algorithm to compute who plays who calculates it).

I want to know how many games a team plays, so I wrote 4 queries:

qryTeam1Games:

SELECT tbl_Fixtures.team1_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;

qryTeams2Games:

SELECT tbl_Fixtures.team2_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;

qryAllGames:

SELECT *
FROM qryTeam1Games 
UNION 
SELECT *
FROM qryTeam2games;

qryNumGamesByTeam:

SELECT qryAllGames.team_id, Sum(qryAllGames.games) AS SumOfgames
FROM qryAllGames
GROUP BY qryAllGames.team_id;

Unfortunately, if the same team_id and games combination appears in both qryTeams1Games and qryTeams2Games, it doesn't bring 2 records into qryAllGames and the summed number of games is only half of what it should be.

Is this a case where a union query shouldn't be used? Is that how it is supposed to work?

A work around isn't a problem but this did have me stumped for a bit.

 

[pbaldy]

UNION will by default eliminate duplicates. Try changing

UNION

to

UNION ALL

 

[petehilljnr]

Excellent - cheers for your advice.

Pete.