Re: [SQL] Stuck on SQL - Any Takers? - Mailing list pgsql-sql

Here are the tables. The first table is not really designed very well. Each
record has nine fields that has a athlete_no in it. That athlete_no relates
to tbl_athlete_stats.

For each record in tbl_teams, I want to pull the corresponding scores out of
tbl_athlete_stats and create a new table, tbl_ranked_teams2.

My query (at the bottom) does not error out, but it never inserts any
records or completes.

tbl_teams (
fld_team_no int primary key,
fld_cust_no int,
fld_athlete1 int, --> fld_player_number in tbl_athlete_stats
fld_athlete2 int, --> fld_player_number in tbl_athlete_stats
fld_athlete3 int, --> fld_player_number in tbl_athlete_stats
fld_athlete4 int, --> fld_player_number in tbl_athlete_stats
fld_athlete5 int, --> fld_player_number in tbl_athlete_stats
fld_athlete6 int, --> fld_player_number in tbl_athlete_stats
fld_athlete7 int, --> fld_player_number in tbl_athlete_stats
fld_athlete8 int, --> fld_player_number in tbl_athlete_stats
fld_athlete9 int  --> fld_player_number in tbl_athlete_stats
);

tbl_athlete_stats (
 fld_player_number int primary key,
 fld_player_last text,
 fld_player_first text,
 fld_player_id int,
 fld_team text,
 fld_position text,
 fld_batting_ave float4,
 fld_singles int,
 fld_doubles int,
 fld_triples int,
 fld_home_runs int,
 fld_rbis int,
 fld_walks int,
 fld_teals int,
 fld_runs int,
 fld_total_points_wk int,
 fld_total_points_ytd int
 );

create table tbl_ranked_teams2 (
fld_team_no int,
fld_athlete1_ytd int,
fld_athlete2_ytd int,
fld_athlete3_ytd int,
fld_athlete4_ytd int,
fld_athlete5_ytd int,
fld_athlete6_ytd int,
fld_athlete7_ytd int,
fld_athlete8_ytd int,
fld_athlete9_ytd int
);

insert into tbl_ranked_teams2
SELECT
tbl_teams.fld_team_no,
tbl_athlete_stats_1.fld_total_points_ytd,
tbl_athlete_stats_2.fld_total_points_ytd,
tbl_athlete_stats_3.fld_total_points_ytd,
tbl_athlete_stats_4.fld_total_points_ytd,
tbl_athlete_stats_5.fld_total_points_ytd,
tbl_athlete_stats_6.fld_total_points_ytd,
tbl_athlete_stats_7.fld_total_points_ytd,
tbl_athlete_stats_8.fld_total_points_ytd,
tbl_athlete_stats_9.fld_total_points_ytd
FROM
tbl_athlete_stats tbl_athlete_stats_1,
tbl_athlete_stats tbl_athlete_stats_2,
tbl_athlete_stats tbl_athlete_stats_3,
tbl_athlete_stats tbl_athlete_stats_4,
tbl_athlete_stats tbl_athlete_stats_5,
tbl_athlete_stats tbl_athlete_stats_6,
tbl_athlete_stats tbl_athlete_stats_7,
tbl_athlete_stats tbl_athlete_stats_8,
tbl_athlete_stats tbl_athlete_stats_9
WHERE
tbl_athlete_stats_1.fld_player_number = tbl_teams.fld_athlete1 and
tbl_athlete_stats_2.fld_player_number = tbl_teams.fld_athlete2 and
tbl_athlete_stats_3.fld_player_number = tbl_teams.fld_athlete3 and
tbl_athlete_stats_4.fld_player_number = tbl_teams.fld_athlete4 and
tbl_athlete_stats_5.fld_player_number = tbl_teams.fld_athlete5 and
tbl_athlete_stats_6.fld_player_number = tbl_teams.fld_athlete6 and
tbl_athlete_stats_7.fld_player_number = tbl_teams.fld_athlete7 and
tbl_athlete_stats_8.fld_player_number = tbl_teams.fld_athlete8 and
tbl_athlete_stats_9.fld_player_number = tbl_teams.fld_athlete9;