help with insert statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

help with insert statement

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-28 : 13:41:57

Hi,

I have 3 tables, and I'm trying to insert results from one of them into another table, but pretty stuck on the syntax here.

The three tables are as below.

In the first table we have a list of "battles", we can calculate the winner by getting the count of votes for each candidate by looking at the 2nd table "tblBattle_votes".

For each battle that exists, I want to insert into tblBattle_Results. I just need to know who the winner is, this is determined by who has the greater amount of votes.

(If the results are tied, we will skip the insert)

thanks once again, much appreciated!
Mike123

(main table)
CREATE TABLE [dbo].[tblBattles](
[battleID] [int] IDENTITY(1,1) NOT NULL,
[challenger_userID] [int] NOT NULL,
[opponent_userID] [int] NOT NULL
) ON [PRIMARY]

GO

(vote results)
CREATE TABLE [dbo].[tblBattle_Votes](
[voteID] [int] IDENTITY(1,1) NOT NULL,
[battleID] [int] NOT NULL,
[voter_userID] [int] NOT NULL,
[winner_userID] [int] NOT NULL
) ON [PRIMARY]

(insert / update into)
CREATE TABLE [dbo].[tblBattle_Results](
[winner_userID] [int] NOT NULL,
[loser_userID] [int] NOT NULL
)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 14:43:47

You probably want a BattleID in the tblBattle_Results table/view.

shaunc
Starting Member

28 Posts

Posted - 2008-10-28 : 16:15:27

Assuming there's a battleID in tblBattle_Results...

WITH results (battle, player, numVotes) AS
(
SELECT DISTINCT battleID, winner_userID, count(voteID) cnt
FROM tblBattle_Votes
GROUP BY battleID, winner_userID
)

INSERT tblBattle_Results
SELECT a.battle AS battleID, a.player AS winner_userID, b.player AS loser_userID
FROM results a
JOIN results b ON a.battle = b.battle AND a.player <> b.player
WHERE a.numVotes = (SELECT MAX(numVotes) FROM results WHERE battle = a.battle)

Probably an easier way but I'm on a CTE kick lately...

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 16:39:01

[code]
insert into tblBattle_Results
select * from (
select a.battleID,
case when sum(case when challenger_userID = winner_userID then 1 else 0 end) > sum(case when opponent_userID = winner_userID then 1 else 0 end) then challenger_userID
when sum(case when challenger_userID = winner_userID then 1 else 0 end) < sum(case when opponent_userID = winner_userID then 1 else 0 end) then opponent_userID
else 0 end as winner,
case when sum(case when challenger_userID = winner_userID then 1 else 0 end) < sum(case when opponent_userID = winner_userID then 1 else 0 end) then challenger_userID
when sum(case when challenger_userID = winner_userID then 1 else 0 end) > sum(case when opponent_userID = winner_userID then 1 else 0 end) then opponent_userID
else 0 end as loser
from
tblBattles a, tblBattle_Votes b
where a.battleID = b.BattleID
group by a.battleID, challenger_userID, opponent_userID
)t where winner <> loser
[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-30 : 05:16:02

Hi Hanbingl,

worked perfectly from what I can tell so far, and very fast...

thank you very much!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources