How to add new columns into SQL Table those to be named as values from existing column

6502 · 09-17-2021, 10:30 AM

I have an existing table TEAMS :

Code:

TEAM_ID, 
TEAM_NAME, 
MATCH_COUNT, 
SCORED_PLUS, 
SCORED_MINUS, 
SCORED_SUM, 
POINTS_WON

The column TEAM_ID contains following numbers:

Code:

I need to get these numbers from column TEAM_ID and to add new columns into table TEAMS :

Code:

TEAM_ID, 
TEAM_NAME, 
MATCH_COUNT, 
SCORED_PLUS, 
SCORED_MINUS, 
SCORED_SUM, 
POINTS_WON,
1,
2,
4,
5,
6,
7,
10,
11,
12,
17,
22,
23,
24,
25

something like next code :

Code:

ALTER TABLE `TEAMS` ADD COLUMNS SELECT TEAM_ID FROM `TEAMS` smallint

The code does not work. How to add new columns into SQL Table, as I described above ?

NevemTeve · 09-17-2021, 11:12 AM

Please don't. This is not how relational databases are supposed to be used.

michaelk · 09-17-2021, 11:17 AM

I agree with NevemTeve.

Out of curiosity why do you need a separate column for each team ID. Without knowing anything about what your trying to accomplish it appears to be redundant and that all you really need is another column.

boughtonp · 09-17-2021, 11:24 AM

As stated, this is the definitely the wrong approach for whatever you're trying to do, but without knowing what that is we can't usefully point you towards a solution.

Read https://xyproblem.info then tell us why you think you need those columns and someone might be able to help.

6502 · 09-17-2021, 11:35 AM

Quote:

Originally Posted by michaelk

I agree with NevemTeve.

Out of curiosity why do you need a separate column for each team ID. Without knowing anything about what your trying to accomplish it appears to be redundant and that all you really need is another column.

It is very easy to do order by two columns:
$sql = "SELECT TEAM_NAME, MATCH_COUNT, POINTS_WON, SCORED_SUM FROM TEAMS ORDER BY POINTS_WON DESC, SCORED_SUM DESC";

But sometimes two teams have equal points won and equal scored sum, in that case the only way to order teams correctly is to know result/s of match/es between these two teams.
I am trying to create information for every team_id match against every other team_id (excluding itself - match team_id=1 vs team_id=1 will never happen, but it will happen match team_id=1 vs team_id=2, team_id=1 vs team_id=4 and so on)
I am planning to use that info for correct order of teams.

I have another table where I insert info about matches MATCHES:

Code:

MATCH_ID, 
TEAM_ID1, 
TEAM_ID2, 
POINTS_1, 
POINTS_2, 
SCORED_1, 
SCORED_2, 
ROUND_NUM

By Trigger before insert for table MATCHES there edits the table TEAMS and update the fields :
MATCH_COUNT, SCORED_PLUS, SCORED_MINUS, SCORED_SUM, POINTS_WON.
My Brain can not decide how to use info from table MATCES, so I decided to restructure the table TEAMS and insert additional info there.

I hope I described my problem to order some teams that have equal points won and equal scored sum by info about direct matches between them.

sundialsvcs · 09-17-2021, 07:03 PM

All that being said, this sounds like the "pivot table" requirement that some SQL implementations do provide.

For instance: "PIVOT and UNPIVOT in Microsoft SQL Server."

Yes, sometimes there is a perceived-legitimate requirement for an SQL server to do for a spreadsheet what it cannot do for itself, due to data-volume and so forth.

There have been various other discussions of the subject in specific reference to MySQL, such as this one. They are not nearly as refined, but they do seem to get the job done.

scasey · 09-17-2021, 07:19 PM

Join table MATCHES with TEAMS. Pseudocode:

Code:

Select (cols) from TEAMS t (cols) from MATCHES m where t.TEAM_ID = m.TEAM_ID1…

Refine as needed to get the data you want in the result set. There’s no need to modify your tables.

6502 · 09-18-2021, 12:23 AM

Quote:

Originally Posted by scasey

Join table MATCHES with TEAMS. Pseudocode:

Code:

Select (cols) from TEAMS t (cols) from MATCHES m where t.TEAM_ID = m.TEAM_ID1…

Refine as needed to get the data you want in the result set. There’s no need to modify your tables.

Code:

SELECT TEAM_NAME, MATCH_COUNT, POINTS_WON, SCORED_SUM FROM TEAMS ORDER BY POINTS_WON DESC, SCORED_SUM DESC

above works fine when POINTS_WON are different for all the teams, or at least SCORED_SUM are different for teams that have equal POINTS_WON.

About more complicated cases when some teams have equal POINTS_WON and equal SCORED_SUM I need to use table MATCHES :
TEAM_ID1 is host for the match, TEAM_ID2 is guest for the match;
POINTS_1 is points won for host after the match, POINTS_2 is points won for guest after the match;
There are possible cases about every pair of teams:
1). No one match is happen at current moment of the competition;
2). 1 match is happen at current moment of the competition;
3). 2 matches are happen at current moment of the competition;
For case 3). is difficult to calculate the final result between 2 teams, because for the first match one team was host TEAM_ID1 and for the second match other team was host TEAM_ID1 ....
I can not decide how to use information from table MATCHES to achieve precise ordering of the TEAMS

Code:

select TEAM_NAME, MATCH_COUNT, POINTS_WON, SCORED_SUM from TEAMS t TEAM_ID1, TEAM_ID2, POINTS_1, POINTS_2 from MATCHES m ORDER BY POINTS_WON DESC, SCORED_SUM DESC, ................

scasey · 09-18-2021, 05:31 AM

To join tables the command needs to contain WHERE clause(s) that define the join…the relationship(s) between the tables. Your last example will just list all the rows in each table.

Read up on using JOIN…also, when using aliases (the t and m in my example) for table names, you need to supply the alias for each column. (Technically only if the same column name exists in both tables, but readability is greatly improved if each column is identified.)

AnanthaP · 09-19-2021, 01:57 AM

Hi 6502 (original poster).

I agree fully with all the persons who said don't go this way.

You are denormalising the database. What happens when a row with TEAM_ID 26 is added OR team_id 3 is revived? Will you alter the table or table structure?

6502 · 09-19-2021, 09:01 AM

Quote:

Originally Posted by AnanthaP

Hi 6502 (original poster).

I agree fully with all the persons who said don't go this way.

You are denormalising the database. What happens when a row with TEAM_ID 26 is added OR team_id 3 is revived? Will you alter the table or table structure?

Hi AnanthaP,
Thanks for the post.
The real names of my tables are:
2021_2022_TEAMS
and
2021_2022_MATCHES
For example it is possible to be revived TEAM_ID = 3, or to be added TEAM_ID = 26, but in another table which will be 2022_2023_TEAMS (for next season). This is not a problem for now.
I will not alter table 2021_2022_TEAMS - I abandoned this stupid idea to alter table 2021_2022_TEAMS.
I will read on using JOIN as I was advised. I hope I will do it successfully.

scasey · 09-19-2021, 10:01 PM

Some further thoughts:
TEAMS is maintained by a trigger on INSERT to MATCHES. It appears to be a summary of the team's MATCHES history, yes?**
Your new requirement wants detail information that TEAMS doesn't have, so TEAMS can't do what you're asking...you'll need to go to the detail source, which is MATCHES...
So...you don't need TEAMS at all. Just get the information you need from MATCHES. Something like:

Code:

SELECT SUM(POINTS_1) from MATCHES group by TEAM_ID1...

I don't know, of course, what the data is in POINTS_1/2 or SCORED_1/2 and how they apply to the solution you desire. Just pointing out that if the summary data doesn't give you what you need, then go to the detail.
So, JOIN isn't necessary if you're not using TEAMS, but you'll need to understand how SUM and GROUP BY work.

**As an aside, I wouldn't have created a summary table at all. I would have built a view over MATCHES to provide the summary. That would require that summary code to be executed every time the view is called, and there is some overhead in maintaining the view, but I suspect that 1) the volume in MATCHES is fairly low and 2) the access to the data is relatively infrequent. Yes?

HTH

6502 · 09-20-2021, 06:22 AM

Quote:

Originally Posted by scasey

Some further thoughts:
TEAMS is maintained by a trigger on INSERT to MATCHES. It appears to be a summary of the team's MATCHES history, yes?**

Yes, TEAMS contains the names of the teams and current amounts of matches played on, points won, goals scored to others, goals scored from others and goal difference for each team, which is 99% information about the ranking of the teams. The main purpose of the tables TEAMS and MATCHES is to show correct ranking of all the teams. If all the teams have different current points won the ranking of the teams would be correct using very simple code SQL:

Code:

SELECT TEAM_NAME, MATCH_COUNT, POINTS_WON, SCORED_SUM FROM TEAMS ORDER BY POINTS_WON DESC, SCORED_SUM DESC

Unfortunately often happen that some teams have equal points won and they have equal goal difference - these cases are very confused to me about to achieve correct ranking of the teams, that is the reason to use detailed information from table MATCHES. For example, today's ranking of English Premiere league:

Code:

1. Chelsea mathes=5, 12:1, points=13;
1. Liverpool mathes=5, 12:1, points=13; 
3. Man United mathes=5, 13:4, points=13;

The reason that Chelese is ranked as №1 and Liverpool is ranked as №1 is direct match Chelsea - Liverpool was draw at 28-aug-2021 (1-1). and goal difference is 12-1=11. Man United is №3 because goal difference is 13-4=9.

Quote:

Originally Posted by scasey

Some further thoughts:
Your new requirement wants detail information that TEAMS doesn't have, so TEAMS can't do what you're asking...you'll need to go to the detail source, which is MATCHES...
So...you don't need TEAMS at all. Just get the information you need from MATCHES. Something like:

Code:

SELECT SUM(POINTS_1) from MATCHES group by TEAM_ID1...

I don't know, of course, what the data is in POINTS_1/2 or SCORED_1/2 and how they apply to the solution you desire. Just pointing out that if the summary data doesn't give you what you need, then go to the detail.
So, JOIN isn't necessary if you're not using TEAMS, but you'll need to understand how SUM and GROUP BY work.
HTH

I use TEAMS at least to get the names of teams. TEAM_ID1 = host TEAM_ID for the current match, POINTS_1 are the points that host team wins at the end of current match 0-lose, 1-draw, 3-win. SCORED_1 is count of scored goals from players of host team = TEAM_1 for current match.
TEAM_ID2 = guest TEAM_ID for the current match, POINTS_2 are the points that guest team wins at the end of current match 0-lose, 1-draw, 3-win. SCORED_2 is count of scored goals from players of guest team = TEAM_2 for current match.
During the second half of competition season these two teams will have another match that they will change the host and the guest.

Quote:

Originally Posted by scasey

Some further thoughts:

**As an aside, I wouldn't have created a summary table at all. I would have built a view over MATCHES to provide the summary. That would require that summary code to be executed every time the view is called, and there is some overhead in maintaining the view, but I suspect that 1) the volume in MATCHES is fairly low and 2) the access to the data is relatively infrequent. Yes?
HTH

Yes, after the season of competition finish there will be 182 matches into table MATCHES.

boughtonp · 09-20-2021, 08:25 AM

A quick reminder that SQL is not case-sensitive, and full uppercase lines can be harder to read.

In the interests of readability I recommend any of the following:

Code:

SELECT team_name, match_count, points_won, scored_sum FROM teams ORDER BY points_won DESC, scored_sum DESC

select TEAM_NAME, MATCH_COUNT, POINTS_WON, SCORED_SUM from TEAMS order by POINTS_WON desc, SCORED_SUM desc

select team_name, match_count, points_won, scored_sum
from teams
order by points_won desc, scored_sum desc

Also, if this is an attempt to build something practical (rather than solely a learning exercise), it's probably useful to look at what http://openfootball.github.io/ offers, or at least how they do the same calculations.