Re: Struggling with a query

[Matthew Southgate]

Hi Peter,

The creation of these columns with sequential suffixes is purely just a function of me trying to achieve my goal. It's extremely wasteful and when you see the CREATE TABLE statements you'll see this is part of a much larger problem.

TABLE27:
CREATE TABLE `TABLE27` (
`ROW_INDEX` varchar(33) DEFAULT NULL,
`ROW_STATUS` varchar(4) DEFAULT NULL,
`INCLUDE_FIRST_6_GAMES` varchar(1) DEFAULT NULL,
`SEASON` varchar(7) DEFAULT NULL,
`DIVISION` varchar(3) DEFAULT NULL,
`DIVISION_` varchar(20) DEFAULT NULL,
`DATE_` varchar(10) DEFAULT NULL,
`MONTH_` varchar(9) DEFAULT NULL,
`DAY_OF_WEEK` varchar(9) DEFAULT NULL,
`HOMETEAM` varchar(14) DEFAULT NULL,
`AWAYTEAM` varchar(14) DEFAULT NULL,
`FTHG` int(1) DEFAULT NULL,
`FTAG` int(1) DEFAULT NULL,
`FTR` varchar(1) DEFAULT NULL,
`HT_LEAGUE_POS_1` int(2) DEFAULT NULL,
`HT_LEAGUE_POS_2` varchar(5) DEFAULT NULL,
`HT_LEAGUE_POS_3` varchar(11) DEFAULT NULL,
`AT_LEAGUE_POS_1` int(2) DEFAULT NULL,
`AT_LEAGUE_POS_2` varchar(5) DEFAULT NULL,
`AT_LEAGUE_POS_3` varchar(11) DEFAULT NULL,
`LEAGUE_POS_DIFF_BAND` varchar(12) DEFAULT NULL,
`MAX_HW_ODDS_BAND` varchar(18) DEFAULT NULL,
`MAX_DR_ODDS_BAND` varchar(20) DEFAULT NULL,
`MAX_AW_ODDS_BAND` varchar(18) DEFAULT NULL,
`HW_RET` decimal(4,2) DEFAULT NULL,
`DR_RET` decimal(4,2) DEFAULT NULL,
`AW_RET` decimal(4,2) DEFAULT NULL,
`RESLS` varchar(7) DEFAULT NULL,
`RESSBL` varchar(7) DEFAULT NULL,
`REVRESLS` varchar(7) DEFAULT NULL,
`REVRESSBL` varchar(7) DEFAULT NULL,
`REVRESTS` varchar(7) DEFAULT NULL,
`HT_WINS_IN_LAST_6` int(3) DEFAULT NULL,
`AT_WINS_IN_LAST_6` int(3) DEFAULT NULL,
`HT_WINS_IN_LAST_6_AT_H` int(3) DEFAULT NULL,
`AT_WINS_IN_LAST_6_AT_A` int(3) DEFAULT NULL,
`HT_WIN_PCT_BAND` varchar(7) DEFAULT NULL,
`HT_H_GAMES_WIN_PCT_BAND` varchar(29) DEFAULT NULL,
`AT_WIN_PCT_BAND` varchar(7) DEFAULT NULL,
`AT_A_GAMES_WIN_PCT_BAND` varchar(29) DEFAULT NULL,
`SSB_RATING_BAND` varchar(17) DEFAULT NULL,
`SSB_HVA_RATING_BAND` varchar(17) DEFAULT NULL,
`BU_RATING_BAND` varchar(17) DEFAULT NULL,
`BU_HVA_RATING_BAND` varchar(17) DEFAULT NULL,
`MAX_HW_ODDS` decimal(3,2) DEFAULT NULL,
`MAX_DR_ODDS` decimal(4,2) DEFAULT NULL,
`MAX_AW_ODDS` decimal(4,2) DEFAULT NULL,
`SP_RATING_BAND` varchar(17) DEFAULT NULL,
`PP_RATING_BAND` varchar(1) DEFAULT NULL,
`HT_TG_AVG_PTS_PER_GAME` varchar(9) DEFAULT NULL,
`HT_HG_AVG_PTS_PER_GAME` varchar(14) DEFAULT NULL,
`HT_AG_AVG_PTS_PER_GAME` varchar(9) DEFAULT NULL,
`HT_TG_AVG_GLSF_PER_GAME` varchar(9) DEFAULT NULL,
`HT_HG_AVG_GLSF_PER_GAME` varchar(14) DEFAULT NULL,
`HT_AG_AVG_GLSF_PER_GAME` varchar(9) DEFAULT NULL,
`HT_TG_AVG_GLSA_PER_GAME` varchar(9) DEFAULT NULL,
`HT_HG_AVG_GLSA_PER_GAME` varchar(14) DEFAULT NULL,
`HT_AG_AVG_GLSA_PER_GAME` varchar(9) DEFAULT NULL,
`HT_TG_AVG_SHTSF_PER_GAME` varchar(11) DEFAULT NULL,
`HT_HG_AVG_SHTSF_PER_GAME` varchar(14) DEFAULT NULL,
`HT_AG_AVG_SHTSF_PER_GAME` varchar(11) DEFAULT NULL,
`HT_TG_AVG_SHTSA_PER_GAME` varchar(11) DEFAULT NULL,
`HT_HG_AVG_SHTSA_PER_GAME` varchar(14) DEFAULT NULL,
`HT_AG_AVG_SHTSA_PER_GAME` varchar(11) DEFAULT NULL,
`HT_TG_SHTSONTF_PCT` varchar(13) DEFAULT NULL,
`HT_HG_SHTSONTF_PCT` varchar(14) DEFAULT NULL,
`HT_AG_SHTSONTF_PCT` varchar(13) DEFAULT NULL,
`HT_TG_SHTSONTA_PCT` varchar(13) DEFAULT NULL,
`HT_HG_SHTSONTA_PCT` varchar(14) DEFAULT NULL,
`HT_AG_SHTSONTA_PCT` varchar(13) DEFAULT NULL,
`HT_TG_AVG_SHTSF_PER_GF` varchar(14) DEFAULT NULL,
`HT_HG_AVG_SHTSF_PER_GF` varchar(14) DEFAULT NULL,
`HT_AG_AVG_SHTSF_PER_GF` varchar(14) DEFAULT NULL,
`HT_TG_AVG_SHTSA_PER_GA` varchar(16) DEFAULT NULL,
`HT_HG_AVG_SHTSA_PER_GA` varchar(16) DEFAULT NULL,
`HT_AG_AVG_SHTSA_PER_GA` varchar(16) DEFAULT NULL,
`HT_TG_SHTSF_OVER_SHTSA` varchar(9) DEFAULT NULL,
`HT_HG_SHTSF_OVER_SHTSA` varchar(14) DEFAULT NULL,
`HT_AG_SHTSF_OVER_SHTSA` varchar(9) DEFAULT NULL,
`AT_TG_AVG_PTS_PER_GAME` varchar(9) DEFAULT NULL,
`AT_HG_AVG_PTS_PER_GAME` varchar(9) DEFAULT NULL,
`AT_AG_AVG_PTS_PER_GAME` varchar(14) DEFAULT NULL,
`AT_TG_AVG_GLSF_PER_GAME` varchar(9) DEFAULT NULL,
`AT_HG_AVG_GLSF_PER_GAME` varchar(9) DEFAULT NULL,
`AT_AG_AVG_GLSF_PER_GAME` varchar(14) DEFAULT NULL,
`AT_TG_AVG_GLSA_PER_GAME` varchar(9) DEFAULT NULL,
`AT_HG_AVG_GLSA_PER_GAME` varchar(9) DEFAULT NULL,
`AT_AG_AVG_GLSA_PER_GAME` varchar(14) DEFAULT NULL,
`AT_TG_AVG_SHTSF_PER_GAME` varchar(11) DEFAULT NULL,
`AT_HG_AVG_SHTSF_PER_GAME` varchar(11) DEFAULT NULL,
`AT_AG_AVG_SHTSF_PER_GAME` varchar(14) DEFAULT NULL,
`AT_TG_AVG_SHTSA_PER_GAME` varchar(11) DEFAULT NULL,
`AT_HG_AVG_SHTSA_PER_GAME` varchar(10) DEFAULT NULL,
`AT_AG_AVG_SHTSA_PER_GAME` varchar(14) DEFAULT NULL,
`AT_TG_SHTSONTF_PCT` varchar(13) DEFAULT NULL,
`AT_HG_SHTSONTF_PCT` varchar(13) DEFAULT NULL,
`AT_AG_SHTSONTF_PCT` varchar(14) DEFAULT NULL,
`AT_TG_SHTSONTA_PCT` varchar(13) DEFAULT NULL,
`AT_HG_SHTSONTA_PCT` varchar(13) DEFAULT NULL,
`AT_AG_SHTSONTA_PCT` varchar(14) DEFAULT NULL,
`AT_TG_AVG_SHTSF_PER_GF` varchar(14) DEFAULT NULL,
`AT_HG_AVG_SHTSF_PER_GF` varchar(14) DEFAULT NULL,
`AT_AG_AVG_SHTSF_PER_GF` varchar(14) DEFAULT NULL,
`AT_TG_AVG_SHTSA_PER_GA` varchar(16) DEFAULT NULL,
`AT_HG_AVG_SHTSA_PER_GA` varchar(16) DEFAULT NULL,
`AT_AG_AVG_SHTSA_PER_GA` varchar(16) DEFAULT NULL,
`AT_TG_SHTSF_OVER_SHTSA` varchar(9) DEFAULT NULL,
`AT_HG_SHTSF_OVER_SHTSA` varchar(9) DEFAULT NULL,
`AT_AG_SHTSF_OVER_SHTSA` varchar(14) DEFAULT NULL,
`ROW_TYPE` int(1) DEFAULT NULL,
`HW_BOOKMAKER` varchar(18) DEFAULT NULL,
`DR_BOOKMAKER` varchar(12) DEFAULT NULL,
`AW_BOOKMAKER` varchar(12) DEFAULT NULL,
`ODDS_UPDATE_TIME` varchar(19) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

TABLE28:
CREATE TABLE `TABLE28` (
`USERNAME` varchar(30) DEFAULT NULL,
`QUERY_DATETIME` varchar(19) DEFAULT NULL,
`SYSTEM_NUMBER` int(1) DEFAULT NULL,
`VARIABLE_NAME` varchar(24) DEFAULT NULL,
`VALUE` varchar(29) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8