SQL to transfer multiple into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL to transfer multiple into one

Author

Topic

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-02 : 12:01:50

Hi,

I am trying to fix my own boneheaded newbie error in database design without having to re-enter the whole works by hand. Here is the situation.

Table: schedules
Field: GameID (unique)
Field: LeagueID
Field: Ref1ID
Field: Ref2ID
Field: LinesMan1ID
Field: LinesMan2ID
Field: ScoreKeeperID

The ids are pulled from separate tables that contain the records of leagues and officials

I realized that this is dumb, as now we are locked into these five roles for officiating. What if we want to create a role for timekeeper or bellydancer? As such, I created two new tables:

Table: roles
RoleID (unique)
LeagueID
RoleName

Table: games_officials
ID (unique)
GameID
LeagueID
OfficialID
RoleID

I populated the roles table with the five standard roles for each of the four leagues we are working with and would now like to perform the following magic: transfer records from the schedules table into the new games_officials table. I need the SQL to create multiple records for each distinct gameid with a correlating official id that is pulled from each of the five roles mentioned above. AND... not all the fields in the schedules table have values, as many games are without linesman. I would prefer that the gameIDs are put in one after the other and not all over the place. Is this possible with one SQL statement? How do I do this?

I am using SQL Server 2008 and running the SQL through coldfusion.

Help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:10:54

how are role names given currently? also where will you get officialid from? are id columns in schedules storing names or id?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:14:27

ok or what you can do is


insert into games_officials
(
GameID,
LeagueID,
OfficialID,
RoleID
)
select s.GameID,
s.LeagueID,
<value for OfficialID>,
r.RoleID
from schedules s
join roles r
on r.LeagueID=s.LeagueID

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-02 : 12:19:44

Wow... lightening fast response time...

To answer your questions: the ref1id, ref2id and so forth columns in the schedules table are storing ids from the officials table, that contains their actual names. The name of the role was previously just assigned via the field in the schedules table. However, now I have created that other table where an id is assigned to a RoleName.

I see where you are going with this, so how do I create a new record in the games_officials table for each ID stored in one of those fields in the schedules table.

insert into games_officials
(
GameID,
LeagueID,
OfficialID,
RoleID
)
select s.GameID,
s.LeagueID,
<value for OfficialID>, (this has to see if there is a value in one of those five fields, and if so, to write it in a new record with a corresponding gameid and leagueid)
r.RoleID
from schedules s
join roles r
on r.LeagueID=s.LeagueID

Appreciate you helping me with this. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:29:47

i think what you're looking for is this


insert into games_officials
(
GameID,
LeagueID,
OfficialID,
RoleID
)
select s.GameID,
s.LeagueID,
case r.RoleName
when 'Ref1' then  Ref1ID
when 'Ref2' then  Ref2ID
when 'LinesMan1' then  LinesMan1ID
when 'LinesMan2' then  LinesMan2ID
when 'ScoreKeeper' then  ScoreKeeperID
end,
r.RoleID
from schedules s
join roles r
on r.LeagueID=s.LeagueID

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-02 : 12:33:41

okay... going to try that now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:36:32

cool..let us know how you got on

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-02 : 13:07:48

Okay... that was cool. It worked. However, here's a catch... can you account for NULL values in the original schedules table (many of the scheduled games did not have linesman for instance) and if that value is NULL, to not write a record into the games_officials table? Right now, it wrote a record for each of the five roles, and included NULL for the positions that were left out in the schedules table for that game.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 13:11:24

ok here it is


insert into games_officials
(
GameID,
LeagueID,
OfficialID,
RoleID
) 
select *
from
(
select s.GameID,
s.LeagueID,
case r.RoleName
when 'Ref1' then  Ref1ID
when 'Ref2' then  Ref2ID
when 'LinesMan1' then  LinesMan1ID
when 'LinesMan2' then  LinesMan2ID
when 'ScoreKeeper' then  ScoreKeeperID
end AS OfficialID,
r.RoleID
from schedules s
join roles r
on r.LeagueID=s.LeagueID
)t
where OfficialID IS NOT NULL

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-02 : 13:35:09

You know... people dont see the art or the beauty in this craft. That worked perfectly. Thanks for your help. Now on the arduous task of adjusting the pages that display the schedules and update the schedules... might need some help there too ;-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 13:39:40

welcome

. let us know if you face any problems

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-02 : 13:48:33

I am curious though... I understand the statement you have written except for this part:

)t

What function does the 't' serve? How does it work in the statement? Is it an alias?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 13:53:04

yup. its an alias. I've formed a derived table out of main query ,giving it a name t and then excluded NULL valued records from it

TCJohnson
Starting Member

9 Posts

Posted - 2009-10-05 : 03:57:47

After transferring all of the official records to this new table, I modified the sql statement to recover the data. It works but I wouldnt mind if someone could take a look at it to see if this is the best way to go about this. Here is the sql:

SELECT S.GameID
, S.LeagueID
, S.ArenaID
, S.GameTypeID
, S.DatePlayed
, S.TimePlayed
, S.HomeTeamID
, S.VisitorTeamID
, S.Cancelled
, GMO.GameID
, O.OfficialID
, O.OfficialName
, HomeTeam.TeamName AS HomeTeamName
, VisitorTeam.TeamName AS VisitorTeamName
, L.LeagueName
, A.ArenaName
, GT.GameType
FROM dbo.schedules S
JOIN arenas A ON S.ArenaID = A.ArenaID
JOIN leagues L ON S.LeagueID = L.LeagueID
JOIN GameTypes AS GT ON S.GameTypeID = GT.GameTypeID
JOIN games_officials GMO ON S.GameID = GMO.GameID
JOIN officials O ON GMO.OfficialID = O.OfficialID
JOIN teams AS HomeTeam ON S.HomeTeamID = HomeTeam.TeamID
JOIN teams AS VisitorTeam ON S.VisitorTeamID = VisitorTeam.TeamID
WHERE S.LeagueID = <cfqueryparam value="#SESSION.LeagueID#" cfsqltype="cf_sql_integer">
<cfif IsDefined("URL.OfficialID")>
AND EXISTS (SELECT GameID, OfficialID from dbo.games_officials WHERE GameID = S.GameID and OfficialID = <cfqueryparam value="#URL.OfficialID#" cfsqltype="cf_sql_integer">)
</cfif>
AND S.DatePlayed BETWEEN <cfqueryparam value="#StartDate#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#EndDate#" cfsqltype="cf_sql_date">
ORDER BY DatePlayed ASC, ArenaName ASC, TimePlayed ASC

The new part is where I use EXISTS to pull all of the records from the games_officials table when an official id is included in the URL and where the gameid = gameid and at least one of the records with that game id has the officials id in it. I needed to also display the other officials scheduled for that game. Is this the best way to go about this?

The results page is showing correctly, however the addition of the subquery has messed up my record count. Although an official may only be scheduled for 9 games, because each game shows three officials, the record count shows 27 records returned (as an example). Is there a way to count just the number of unique gameids being returned instead of the whole works.

Your insights are appreciated. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources