Creating a query from two different tables where columns have the same data

[Pettor]

Hello guys, one more time I need your precious help.

I have a table with football match results and I would like to calculate the total statistics for the teams either playing at Home or Away.

So far I have managed to calculate statistics where the teams have played at home or away (it is easy as I group data based on one column) but when it comes to total statistics I can't combine data from the two different columns...

Any ideas? Let's say that the table is as simple as the below.

PS. I could combine the two separate tables I have created with the data for home and away statistics, but in this way, there are teams from the second table that do not exist in the first one and vice versa. Some may have played Away but not yet at Home and this creates more records in one table than the other.

Thanks!

HOME	AWAY	RESULT
Arsenal	Chelsea	1
Chelsea	Arsenal	X
Arsenal	Lester	2
Lester	Arsenal	1

 

[Pettor]

Hello guys,

I think I did it, I had to use the UNION clause to combine the two tables (Home and Away). After some trials, I think I am in a position to combine the Home and Away columns without losing records!

Thank you for your attention though!

 

[jackd]

Here is a link to an older data model related to football/soccer that may be useful to you.

 

[Pettor]

Thank you Jack but I have already tried to build my own model.

Unfortunately I can't still combine the two tables together. Any ideas about that?

I have created two tables A & B.

A, includes the fixtures of the Home team (taken from the master table of results) and B that of the Away team.

Of course, you can have the same teams in both tables in the majority of data, but if a team has only played at Home or Away it may be included only in one of them.

So the issue is how can I combine the two tables and get the combined data per team either played at Home or Away.

I have created the below UNION query to join the tables but I can't combine the numerical data.

For instance, I want to populate the average odds from both tables for the win of the teams. n the A table is it called ODDS_FT_1 and in the B table is called ODDS_FT_2.

How these columns from both tables could be combined to bring the average of both?



SELECT

A.League,
A.Home
FROM
A
LEFT JOIN
B
ON
(A.Home = B.Away) AND (A.League = B.League)
GROUP BY
A.League, B.Home

UNION SELECT

B.League,
B.Away
FROM
A
RIGHT JOIN
B
ON
(A.Home = B.Away) AND (A.League = B.League)
GROUP BY
B.League, B.Away;

 

[jackd]

I gave the link so you could compare your data model. I found this article that may help you with your calculations.