How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?
This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this...
CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)
SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),
TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),
Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),
RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )
FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID
WHERE D.DrawingID = DS.CurrentDrawing
GO"INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
and
"WHERE W.DrawingID = DS.CurrentDrawing"
are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.
As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.|||yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.|||This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:
CREATE PROCEDURE AddDrawStats
AS
Declare @.Players int
Declare @.RegisterPlayers int
Declare @.TicketsPlayed int
set @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
set @.RegisterPlayers = (SELECT Count(*) FROM Student)
set @.TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),
Update Drawing
set WinnersWon = WinnersSubquery.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
from Drawing
inner join
(SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
on Drawing.DrawingID = WinnersSubquery.CurrentDrawing
INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
select WinnersSubquery.CurrentDrawing,
WinnersSubquery.WinnersWon,
@.TicketsPlayed,
@.Players,
@.RegisterPlayers
from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
where Drawing.DrawingID is null|||Thanks for all the help! This works but I have two questions?
Could I have written this Stored procedure better? and...
Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
**SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
CREATE PROCEDURE AddDrawStats2
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT
SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )
UPDATE Drawing
SET WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO|||SET @.Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)|||Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?
CREATE PROCEDURE AddDrawStats
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT
SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )
UPDATE Drawing
SET
WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO
