April 30, 2013 at 5:05 am
Trying to update a table using integer number in 3 columns and a signing texts name to results in the second table TEST2
It only updated that last update statament which was Volunteer. It should upadte with all the other columns from TEST1.
I even try using update with subquery but I got a error to do with multipe values. And I got no idea how to do the if else part of the sql if any other number come up.
Thanks in advance!!
Actual Results by using SQL Query below-
Event_Role
Volunteer
Wanted Results-
Event_Role
Speaker
Speaker
Speaker
Speaker
coordinator
coordinator
coordinator
coordinator
Volunteer
Volunteer
Volunteer
Volunteer
Other
Other
Other
CREATE TABLE [dbo].[TEST1](
[Ispeaker] [int] NOT NULL,
[Iscoordinator] [varchar](35) NULL,
[Volunteer] [int] NULL,)
ON [PRIMARY]
GO
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);
GO
--create empty column in table2
Select CAST(NULL AS VARCHAR(20)) AS Event_Role
into dbo.TEST2
UPDATE [dbo].[TEST2]
SET Event_Role = 'Speaker'
FROM dbo.TEST1
Where Ispeaker = -1
UPDATE [dbo].[TEST2]
SET event_role = 'Coordinator'
FROM dbo.TEST1
WHERE IsCoordinator = -1
UPDATE [dbo].[TEST2]
SET event_role = 'Volunteer'
SELECT ID
FROM DBTEAM.azeez.Participants
WHERE IsVolunteer = -1
---not sure how to create if else statment into the sql query
UPDATE [dbo].[TEST2]
SET event_role = 'Other'
SELECT ID
FROM dbo.TEST1
WHERE ? = >2
------try subquery but does not work because of multipe values errors
UPDATE [dbo].[TEST2]t
SET event_role = 'Speaker'
WHERE t.id IN (SELECT ID
FROM dbo.TEST1
WHERE IsVolunteer = -1)
AND event_role IS NULL
April 30, 2013 at 7:36 am
It is very unclear what you are trying to do but your update statements are full of logic errors. Let's look at them one at a time.
UPDATE [dbo].[TEST2]
SET Event_Role = 'Speaker'
FROM dbo.TEST1
Where Ispeaker = -1
There is nothing to correlate rows from TEST2 to TEST1 so the entire table Event_Role is now Speaker.
UPDATE [dbo].[TEST2]
SET event_role = 'Coordinator'
FROM dbo.TEST1
WHERE IsCoordinator = -1
Same as above but now the table is Coordinator.
UPDATE [dbo].[TEST2]
SET event_role = 'Volunteer'
SELECT ID
FROM DBTEAM.azeez.Participants
WHERE IsVolunteer = -1
This is actually 2 statements. When this runs, the entire TEST2 table will now be Volunteer AND you have a resultset from the select statement.
UPDATE [dbo].[TEST2]
SET event_role = 'Other'
SELECT ID
FROM dbo.TEST1
WHERE ? = >2
This is pretty much the same thing as above.
------try subquery but does not work because of multipe values errors
UPDATE [dbo].[TEST2]t
SET event_role = 'Speaker'
WHERE t.id IN (SELECT ID
FROM dbo.TEST1
WHERE IsVolunteer = -1)
AND event_role IS NULL
I am not really sure what you are trying to do here.
You did a great job posting ddl for TEST1. Unfortunately a lot of your code is using values from other tables and we don't have those. If you can try to explain more clearly what you are trying to do we can probably help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2013 at 2:45 am
Thanks for your response apologise for the last post I had no idea about what I was trying to do. Hopefully the second post from me is more helpful to you.
I have a table similar to the one below, where I have to use the last 6 column to populate the ‘Event Role’ based on the following higher level heirarchy – Speaker
Coordinator
Volunteer
Attendee
Registered and may more later on. For the ‘Participant_ID’ ‘Supporter_ID’ ‘Event_Code’ to be group into them.
Table1
Participant_IDSupporter_IDEvent_CodeEvent_RoleIsCoordinatorSpeakerIsVolunteerIsInstructorAttended_INTRegistered_INT
18940013ER11NULL0-100-12
28939930JR03NULL0-100-13
47044255SR05NULL-100003
46112754SR05NULL-100003
90098624LL08NULL-1000-12
218107660001NULL-1000-12
46192636SR05NULL-100003
18932010CC11NULL-100002
46295285SR05NULL-100003
218889021212NULL0-10003
90126569LL08NULL-1000-13
29023004JR03NULL0-100-13
28795589JR03NULL0-100-13
218803871112NULL0-10003
218804091212NULL0-10003
14248862ON09NULL00-1002
46785510SR05NULL-1000-13
46824546SR05NULL-100003
76653923DC07NULL-100003
218255750712NULL0-100-13
47144913SR05NULL-100003
For example in the ‘Speaker’ column is -1 in table_original it would appear as Speaker in Event_Role column in the ‘Event_Role’ and so on for the other columns (by the way 0 is NULL or nothing) Updates into Event_Role. For the for ‘Registered_INT’ column which also goes into the Event_Role Column and shows up as
‘’1= do not register
“2=NO
“3=YES
By the way a Supporter can attend multiple events so can appear multiple times in a group. It show look some like table below.
Participant_IDSupporter_IDEvent_CodeEvent_RoleIsCoordinatorSpeakerIsVolunteerIsInstructorAttended_INTRegistered_INT
18940013ER11Speaker0-100-12
28939930JR03Speaker0-100-13
46112754SR05Coordinator-100003
90098624LL08Coordinator-1000-12
18932010CC11Do Not register000001
46295285SR05Coordinator-100003
218889021212Speaker0-10003
90126569LL08Attended-1000-13
29023004JR03Speaker0-100-13
29023004JR03Attended0-100-13
28795589JR03Attended0-100-13
28795589JR03Speaker0-100-13
90126569LL08Coordinator-1000-13
218803871112NO000002
14248862ON09Volunteer00-1002
46785510SR05Coordinator-1000-13
46824546SR05Coordinator-100003
76653923DC07Coordinator-100003
218255750712YES0000-13
And help thank again! SQL Code below --
CREATE TABLE [dbo].[table1](
[Participant_ID] [int] NOT NULL,
[Supporter_ID] [int] NULL,
[Event_Code] [varchar](50) NOT NULL,
[Event_Start_Date_ID] [int] NULL,
[Attended_Y_N] [varchar](1) NULL,
[Registration_Date_ID] [int] NULL,
[Registered] [varchar](15) NULL,
[Enquiry_Date_ID] [int] NULL,
[Enquired_Y_N] [varchar](1) NULL,
[Event_Role] [varchar](20) NULL,
[IsCoordinator] [smallint] NOT NULL,
[Speaker] [varchar](20) NULL,
[IsVolunteer] [smallint] NOT NULL,
[IsInstructor] [smallint] NOT NULL,
[Attended_INT] [smallint] NOT NULL,
[Registered_INT] [smallint] NOT NULL
) ON [PRIMARY]
----Insert data into table1
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('1894','0013','ER11','20111015','Y','NULL','N','NULL','NULL','NULL','0','-1','0','0','-1','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2893','9930','JR03','20031204','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4704','4255','SR05','NULL','N','20050802','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4611','2754','SR05','NULL','N','20050802','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('9009','8624','LL08','20080521','Y','NULL','N','NULL','NULL','NULL','-1','0','0','0','-1','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2181','0766','0001','20121206','Y','NULL','N','NULL','NULL','NULL','-1','0','0','0','-1','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4619','2636','SR05','NULL','N','20050802','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('1893','2010','CC11','20111129','N','NULL','N','NULL','NULL','NULL','-1','0','0','0','0','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4629','5285','SR05','NULL','N','20050801','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2188','8902','1212','20121202','N','NULL','Y','NULL','NULL','NULL','0','-1','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('9012','6569','LL08','20080521','Y','20100708','Y','NULL','NULL','NULL','-1','0','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2902','3004','JR03','20031204','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2879','5589','JR03','20031204','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2188','0387','1112','20121125','N','NULL','Y','NULL','NULL','NULL','0','-1','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2188','0409','1212','20121209','N','20121209','Y','NULL','NULL','NULL','0','-1','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('1424','8862','ON09','NULL','N','NULL','N','NULL','NULL','NULL','0','0','-1','0','0','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4678','5510','SR05','NULL','Y','20050802','Y','NULL','NULL','NULL','-1','0','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4682','4546','SR05','NULL','N','20050803','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('7665','3923','DC07','20070421','N','20070315','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2182','5575','0712','20120722','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4714','4913','SR05','NULL','N','20050803','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
May 2, 2013 at 7:23 am
umm....this new problem is not any better than the last one. Your inserts don't work as posted, your sample data compared to your output doesn't seem to match. The description of what you are trying to do here is totally unclear. It seems that your tables are denormalized which is causing you some issues. Please post enough information so that we can understand the problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2013 at 3:22 am
Hopefully the scrip I created below is more helpful. It creates the sample data and the wanted results also in another table. See scrip below.
Thanks
CREATE TABLE [dbo].[table1](
Participant_ID [int] NOT NULL,
[Supporter_ID] [int] NULL,
[Event_Code] [varchar](50) NOT NULL,
[Event_Role] [varchar](20) NULL,
[IsCoordinator] [smallint] NOT NULL,
[Speaker] [varchar](20) NULL,
[IsVolunteer] [smallint] NOT NULL,
[IsInstructor] [smallint] NOT NULL,
[Attended_INT] [smallint] NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[wanted_results](
Participant_ID [int] NOT NULL,
[Supporter_ID] [int] NULL,
[Event_Code] [varchar](50) NOT NULL,
[Event_Role] [varchar](20) NULL,
[IsCoordinator] [smallint] NOT NULL,
[Speaker] [varchar](20) NULL,
[IsVolunteer] [smallint] NOT NULL,
[IsInstructor] [smallint] NOT NULL,
[Attended_INT] [smallint] NOT NULL,
) ON [PRIMARY]
Go
--example data
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','NULL','0','-1','0','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','NULL','0','-1','-1','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','NULL','0','-1','0','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','9930','JR03','NULL','0','-1','0','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4611','2754','SR05','NULL','-1','0','0','0','0');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','NULL','-1','0','-1','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1893','2010','CC11','NULL','0','0','0','0','0');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4629','5285','SR05','NULL','-1','0','0','0','0');
GO
--wanted results is created by inserting data into the 'Event_Role' column where the 5 last columns equals -1.
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Speaker','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Attended_INT','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','Speaker','0','-1','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','Attended_INT','0','-1','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','IsVolunteer','0','-1','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Speaker','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Attended_INT','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','9930','JR03','Speaker','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','9930','JR03','Attended_INT','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4611','2754','SR05','IsCoordinator','-1','0','0','0','0');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','IsCoordinator','-1','0','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','IsVolunteer','-1','0','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','Attended_INT','-1','0','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1893','2010','CC11','NULL','0','0','0','0','0');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4629','5285','SR05','IsCoordinator','-1','0','0','0','0');
May 3, 2013 at 4:50 am
Hi azdeji,
I understand what you are trying to achieve, but you won't be able to do it with an update, since you need to create rows for each possibility, you will have to use unions, I think it would have been a bit better to do it more with a transactional type design, but to get it to do what you want it to do you can use the code below:
SELECT Participant_ID, Supporter_ID, Event_Code, 'Speaker' AS EventRole, IsCoordinator, Speaker, IsVolunteer, IsInstructor, Attended_INT
FROM table1 t
WHERE Speaker = -1
UNION
SELECT Participant_ID, Supporter_ID, Event_Code, 'Attended_INT' AS EventRole, IsCoordinator, Speaker, IsVolunteer, IsInstructor, Attended_INT
FROM table1 t
WHERE Attended_INT = -1
just keep with the unions (copy paste), each time changing the where clause to the next column you want to check for, it will give you the desired result, I know the solution is not dynamic for when you want new "is-something" roles, but the design is already not dynamic in the sense that you have to add a new column for each possibility already,
I hope it answers your question,
Kind regards
May 6, 2013 at 5:44 pm
Thanks c_o, for helping out.
the SQL code works perfectly!!
Do you know how to create a hierarchy group from the event_roles?
Thanks again.
May 6, 2013 at 11:43 pm
Hi Azdeji,
I am glad it worked,
mmm, to do that you may have to change your schema, you will be able to make it work as is, but you are going to create a lot of trouble for yourself going forward, so it will be better to fix it now, but that means your schema will change quite a bit, will that be a problem? I will see if I get a chance today to quickly do it both ways, then you can choose if you want to continue with your current schema or if you would prefer to change it,
😉
May 6, 2013 at 11:57 pm
I would prefer to change the schema now. How would I go about doing that?
Thank you!
May 7, 2013 at 6:44 am
Hi Azedji,
The structure below I think has a bit more flexibility, you will see that you can add a role at any time, there may be better ways of doing the hierarchy select, but the structure will allow for the dynamic addition of new roles, new participants, and anything else you will need, the important table is the mapping table which allows you to say which participants were at which event, then your select handles the rest, you can copy the join for the event join and each time link it to the previous one, which will will take the hierarchy to the next level, for each parent. Like I said, the hierarchy may not be the ultimate solution, but at least your data will be in a much more dynamic structure? Comments welcome.
I don't know if my explanation is good enough, you are welcome to shoot with specific questions on which data you will have to put where to make it work if the structure is not self-explanatory enough,
CREATE TABLE [dbo].[Event]
(
[EventID] INT IDENTITY NOT NULL,
[EventName] VARCHAR(150) NULL
)
CREATE TABLE [dbo].[EventRole]
(
[EventRoleID] INT IDENTITY NOT NULL,
[EventRoleName] VARCHAR(150) NULL,
[EventRoleParentID] INT NULL
)
CREATE TABLE [dbo].[ParticipantMap]
(
[EventID] INT NOT NULL,
[ParticipantID] INT NOT NULL
)
CREATE TABLE [dbo].[Participants]
(
[ParticipantID] INT IDENTITY(1,1) NOT NULL,
[ParticipantName] VARCHAR(50) NOT NULL,
[EventRoleID] INT NOT NULL
)
INSERT INTO [dbo].[Event] VALUES ('Event1')
INSERT INTO [dbo].[Event] VALUES ('Event2')
INSERT INTO [dbo].[Event] VALUES ('Event3')
INSERT INTO [dbo].[Event] VALUES ('Event4')
INSERT INTO [dbo].[EventRole] VALUES ('Speaker',NULL)
INSERT INTO [dbo].[EventRole] VALUES ('Attendent','1')
INSERT INTO [dbo].[EventRole] VALUES ('Instructor',NULL)
INSERT INTO [dbo].[EventRole] VALUES ('Coordinator','3')
INSERT INTO [dbo].[Participants] VALUES ('Person1','1')
INSERT INTO [dbo].[Participants] VALUES ('Person2','1')
INSERT INTO [dbo].[Participants] VALUES ('Person3','2')
INSERT INTO [dbo].[Participants] VALUES ('Person4','2')
INSERT INTO [dbo].[Participants] VALUES ('Person5','3')
INSERT INTO [dbo].[Participants] VALUES ('Person6','3')
INSERT INTO [dbo].[Participants] VALUES ('Person7','4')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','1')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','3')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','4')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','5')
INSERT INTO [dbo].[ParticipantMap] VALUES ('2','1')
INSERT INTO [dbo].[ParticipantMap] VALUES ('2','2')
INSERT INTO [dbo].[ParticipantMap] VALUES ('3','3')
INSERT INTO [dbo].[ParticipantMap] VALUES ('3','4')
INSERT INTO [dbo].[ParticipantMap] VALUES ('3','5')
INSERT INTO [dbo].[ParticipantMap] VALUES ('3','6')
SELECT
EventName
, ParticipantName
, erL1.EventRoleName AS ParticipantRole
, erL2.EventRoleName AS ParticipantParentRole
FROM
[dbo].[Event] e
LEFT JOIN [dbo].[ParticipantMap] pm ON e.EventID = pm.EventID
LEFT JOIN [dbo].[Participants] p ON pm.ParticipantID = p.ParticipantID
LEFT JOIN [dbo].[EventRole] erL1 ON p.EventRoleID = erL1.EventRoleID
LEFT JOIN [dbo].[EventRole] erL2 ON erL1.EventRoleParentID = erL2.EventRoleID
May 7, 2013 at 2:35 pm
Thanks c_o! it works perfectly 😀
just a a few simply questions relating to my actual table which contains 200000+ rows and additional columns I did not include in the sample table.
If wanted to add more groups into EventRole, I would simply use the sql scrip below?
CREATE TABLE [dbo].[Registered]
(
[ParticipantID] INT IDENTITY(1,1) NOT NULL,
[RegisteredInfo] VARCHAR(50) NOT NULL,
[EventRoleID] INT NOT NULL
)
INSERT INTO [dbo].[EventRole] VALUES ('Registered','4')
Do I also need to add every participant level manually? I have 200000+ participant in the table.
INSERT INTO [dbo].[Participants] VALUES ('Person1','1')
INSERT INTO [dbo].[Participants] VALUES ('Person2','1')
INSERT INTO [dbo].[Participants] VALUES ('Person3','2')
INSERT INTO [dbo].[Participants] VALUES ('Person4','2')
ParticipantMap table is simple matching the EventRole to the participant table but is this manually entry also?
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','1')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','3')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','4')
Thanks for the help Again!
May 9, 2013 at 4:06 am
Ya, see, if you already have a data set in your original schema, it makes it a bit more difficult, as you will have to migrate your data to the new schema, if the setup was for an app which will be receiving data for the first time, then it would almost be easier, as you can set up the schema and have the app populate it in a specific format,
To answer the first insert portion, no, the whole idea is to have all your event roles in one table, and to not have to create a table/column for each type, then assigning your new type to a participant,
it basically works like this:
Case 1
--New participant, existing role
----Add participant with id of role to which they belong to in participants table
Case 2
--New participant, new role
----Add role to eventrole table, with the id of the parent role if it has one (say like student, would have id of teacher in parentid, and teacher would have principal's id in parentid, but principal would have null)
----add participant with id of new role created
**This will ensure that you can always add new roles,without having to do any changes to your schema
in your case, you don't have to re-capture all the data, but to migrate may be a bit difficult, depending on your current data structure, there were a few different versions you mentioned previously in your post, so by now I am not sure which one you have currently, but what you basically need to do is to populate the specific fields of the new schema table by doing a select from your current schema table to be in the format of the new schema table
ex. say your current format is in the format where you have an event with different participants and then you have a column which identifies the role of the participant, (as I losely understand, this is how your current schema is), in this case you will do your insert into the new schema participant table as follows:
--say you want to add all the participants who are speakers
INSERT INTO Participants
SELECT ParticipantName
, 1
FROM OriginalTable
WHERE isSpeaker = -1
then from there on, you can do that to "map" each of the participants to a role and thus build your participants table, you will also have to find similar ways of populating the Event and Participant map tables,
I hope it makes sense, it makes it a bit difficult as currently I am not even sure if your original tables are in such a format so that you will be able to do extracts like these, but I am sure if you understand exactly how the new schema works, you will be able to do the mappings like this, and thus populate the tables from your original tables with only a little effort, please do not go and try to re-type everything, or something like that, there is always an easier way
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply