From: Tim Perdue <perdue@raccoon.com>
To: twm139@its.to
To: pgsql-sql@hub.org
Subject: Re: [SQL] Stuck on SQL - Any Takers?
Date: Thu, 18 Mar 1999 22:13:50 -0600
Message-ID: <013b01be71be$e551f0b0$0b8c5aa5@timnt.weather.net> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: perdue@raccoon.com, twm139@its.to, pgsql-sql@hub.org
Subject: Re: [SQL] Stuck on SQL - Any Takers?
In-Reply-To: <013b01be71be$e551f0b0$0b8c5aa5@timnt.weather.net>
References: <013b01be71be$e551f0b0$0b8c5aa5@timnt.weather.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox