Link to home
Create AccountLog in
Avatar of R-Byter
R-ByterFlag for Serbia

asked on

LEFT JOIN on multiple columns

Hello fellow experts.

I'm in some kind of twilight zone here and cant wrap my mind around this. Looks like I'm too tired.
I have two tables:

Squads and Events

Squads

IDsquad
IDplayer
IDteam
IDgame
date

Events

IDevent
IDgame
IDplayer
eventType (goal, foul, card, etc)

Pairs from table Squads (IDgame, IDplayer) are the same ones as pairs in Events (IDgame, IDplayer).
There is fixed number of players, if there is two teams, there are 10 players etc. Player that played one game forTeamA can play next game for TeamB, TeamC etc. Therefore I have pairs.
I want to countnumber of goals that each team scored on every game. For example:

Game1 Team1 7 goals
Game1 Team2 3 goals
Game2 Team1 2 goals
Game2 Team2 5 goals
Game3 Team1 3 goals
Game3 Team2 3 goals

Right now I have this query:

SELECT Count(events.eventType) AS goals, squads.idTeam, squads.idGame
FROM squads LEFT JOIN events ON (events.idPlayer=squads.idPlayer) AND (squads.idGame = events.idGame)
WHERE events.eventType='goal'
GROUP BY squads.idTeam, squads.idGame, events.eventType
ORDER BY squads.idGame, squads.idTeam;

And that works OK if team has scored at least one goal. For example, if TeamA scored 3 goals and TeamB 0 my result look like this:

Game1 TeamA 3 goals

And I want this result:

Game1 TeamA 3 goals
Game2 TeamB 0 goals

I am using MS Access 2003.

Thanks in advance.

Regards
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Is this your full table structure? Or are there tables to store info about Games, Players, Teams, etc?

How are Players related to Squads?

How are Players or Squads related to Teams?

How is a Game related to an Event?

Avatar of R-Byter

ASKER

I have tables Players and Teams.

1

One to many relationship between Players and Squads tables.
One to many relationship between Teams and Squads tables.
One to many relationship between Games and Squads tables.
Do You got it now or do You need more info?
Thanks
You wrote this:

"Pairs from table Squads (IDgame, IDplayer) are the same ones as pairs in Events (IDgame, IDplayer)."

What's the reason for storing the same values in both Squads and Events? It would seem that a "Squad" is composed of several "Players" (since this is apparently some sort of team competition). If that's so, what's the reason for storing IDPlayer in the Event table?

I appreciate you showing the relationships between those items, but I was looking for more of a "real-world" sort of relationship? In other words, do Players make up Teams? Or do Players make up Squads? If so, do Squads make up a Team? Does a Team enter an Event, or does a Squad enter an Event?

What exactly is a "Game", and how is it related to an Event?
Avatar of R-Byter

ASKER

What's the reason for storing the same values in both Squads and Events?

The goal was to avoid use of table Squads when I need to  get some simple statistics but pair in Events table can maybe be replaced with idsquad. Good point.

Here is the story behind this. There are about 20-30 players. Every week there are some games.
Players can play for one team on Day 1 and for other team on Day 2, etc. So, there are no players who play exclusively for one and only team. What I want to achieve is to get statistics about each game, number of goals, fouls, cards, etc.
Table Squads contains info about which player played for which team in some game.
Table Events contains info about players actions at specific games (like scored goals, committed fouls, etc)

Does a Team enter an Event, or does a Squad enter an Event?

Both. :)
For example there is a match between Dolphins and Whales teams. Actions that player did are logged into Events table. Squad is used to describe which players played for specific team on some match.
Here is the real life situation.
On player is member of one team for first half of the season. In the second half of the season he was bought by other team but his goals are still counted as the goals of the team he played before.

What exactly is a "Game", and how is it related to an Event?

Game = Match

Events are, like I said, actions that players do during some match.
A query can't show data that isn't there.  If you want to show a score of zero, you need to join it to another table with a zero value for goals.  Use SUM() instead of Count, then you can do another left join so that all teams show up with a zero in that category.

Basically do two queries and union them together.  First query show all of the goals.
Second query do all of the team/game combos with a zero for the goals

then sum the two together.
Avatar of R-Byter

ASKER

Interesting approach. Cant really do the sum, since I'm counting rows that have "goal" as eventType.
Could You be so kind to write that UNION query?

Regards
Can you post some sample data for the two tables and the expected output?
Avatar of R-Byter

ASKER

See attached text:

Squads

idsquad		idplayer	idTeam	idgame		date

1		1		1	1		21-Dec-2010
2		2		1	1		21-Dec-2010
3		3		1	1		21-Dec-2010
4		4		1	1		21-Dec-2010
5		5		1	1		21-Dec-2010
6		6		1	1		21-Dec-2010
7		7		2	1		21-Dec-2010
8		8		2	1		21-Dec-2010
9		9		2	1		21-Dec-2010
10		10		2	1		21-Dec-2010
11		11		2	1		21-Dec-2010
12		12		2	1		21-Dec-2010
13		1		1	2		22-Dec-2010
14		3		1	2		22-Dec-2010
15		5		1	2		22-Dec-2010
16		6		1	2		22-Dec-2010
17		14		1	2		22-Dec-2010
18		15		1	2		22-Dec-2010
19		7		2	2		22-Dec-2010
20		8		2	2		22-Dec-2010
21		9		2	2		22-Dec-2010
22		10		2	2		22-Dec-2010
23		11		2	2		22-Dec-2010
24		16		2	2		22-Dec-2010



Events

idevent		idgame		idplayer	vrstaDogadjaja

3		1		4		injury
6		1		2		goal	
7		1		1		goal	
8		1		4		goal
9		2		6		goal	
10		2		3		goal	
11		2		9		goal	
12		2		9		goal	
13		2		16		goal	
14		2		16		goal	
15		2		7		head	
16		2		6		ycard
17		2		1		ycard
18		2		1		ycard
19		2		15		head
20		2		14		goal	
21		2		15		goal	
22		2		15		goal	
23		2		15		goal	
24		2		15		goal	
25		1		4		ycard



Results

idteam		idgame		goals

1		1		3
2		1		0
1		2		7
2		2		4

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
See attached.
query.txt
Avatar of R-Byter

ASKER

@awking00:

For some reason Your solution doesn't work (syntax error in from clause). I appreciate Your effort and patience to help me. I'm not quite sure You can construct query like that in Ms Access.

@Sharath_123:

Works like a charm. Simple and effective. That's what I was looking for. You helped me not only to solve this, but to learn again what I forgot.

I need to thank all geniuses and masters that took participation in this question.

Regards
Glad to help you.