Loop or Cursor needed? Questions on SQL Query...
-
Hi, I have a question on a SQL query and thought I would post the information here. Basically, I have three tables. I will keep it as simple as possible but here is the structure.
TableOne - Only has UserID column
TableTwo - Has Column called QuestionID and QuestionName
TableThree - Has column called ResponseID, QuestionID, and UserID along with response.
Each time a user respondes to a question its stored in the response table. Since the response table doesn't store the actual table names I can do a direct query on it. What do I need to do to execute this. I am looking for a returned results set like this:
UserID QuestionName Response
1 Question 1 My Response
1 Question 2 My Response to question #2
2 Question 1 My Response
2 Question 2 question 2 response
Can anyone help? I was thinking it would need a while loop to build a temporary table?
-Chad
-
>>I was thinking it would need a while loop to build a temporary table?
Nope, this is just a simple 3 table join:
Select t.UserID, t2.QuestionName, t3.Response
From TableOne as t1
Inner Join TableThree As t3
On (t1.UserID = t3.UserID)
Inner Join TableTwo As t2
On (t2.QuestionID = t3.QuestionID)
Order By t1.UserID, t2.QuestionName
If you need the resultset to contain unanswered questions, then you need Left Join.
-
I must have been smoking crack! I can't believe this
for some reason I thought it would require some unions but obviously not.Thanks
-
What if I wanted to order it by a particular column description?
Lets say i return these rows:
UserName Question Response
admin Email Address San Diego
admin Password 12345
admin Special Question Yes
user2 Email Address KAnsas City
user2 Password 1234567
user2 Special Question No
What if I wanted to order the results by Password? I can't group them by 'Question' because it would return either Email Address or Special Question (depending on ascending or desc).
-Chad
-
Oh, I think I mis-communicated what I wanted as results and thats why I can't just do the joins. Here is what I want the result set to look like.
UserID, Question1, Question2, Question3
1, Response1, Response2, Response3
2, Responsee, Response2, Response3
Does that help? I need each user to be one single row.
-Chad
-
You need a loop of a kind, yes. It can be done with a function though, cursor is not necessary. Is this what you were looking for?
--testing environment
--TableOne - Only has UserID column
create table users (userid int)
insert into users(userid) values (1)
insert into users(userid) values (2)
insert into users(userid) values (3)
insert into users(userid) values (4)
--TableTwo - Has Column called QuestionID and QuestionName
create table questions (questionid int, questionname varchar(30))
insert into questions (questionid, questionname) values (1, 'First question')
insert into questions (questionid, questionname) values (2, 'Second question')
insert into questions (questionid, questionname) values (3, 'Third question')
--TableThree - Has column called ResponseID, QuestionID, and UserID along with response.
create table responses (responseid int, questionid int, userid int, response varchar(20))
insert into responses (responseid, questionid, userid, response) values (1, 1, 1, 'U1 resp to Q1')
insert into responses (responseid, questionid, userid, response) values (2, 2, 1, 'U1 resp to Q2')
insert into responses (responseid, questionid, userid, response) values (3, 3, 1, 'U1 resp to Q3')
insert into responses (responseid, questionid, userid, response) values (4, 1, 2, 'U2 resp to Q1')
insert into responses (responseid, questionid, userid, response) values (5, 2, 2, 'U2 resp to Q2')
insert into responses (responseid, questionid, userid, response) values (6, 1, 3, 'U3 resp to Q1')
insert into responses (responseid, questionid, userid, response) values (7, 3, 3, 'U3 resp to Q3')
insert into responses (responseid, questionid, userid, response) values (8, 2, 3, 'U3 resp to Q2')
insert into responses (responseid, questionid, userid, response) values (9, 2, 4, 'U4 resp to Q2')
--UDF to concatenate responses
CREATE FUNCTION dbo.get_responses (@user int)
RETURNS varchar(1024)
AS
BEGIN
DECLARE @string VARCHAR(1024)
SELECT @string = ''
SELECT @string = @string + r.response + ', '
FROM users u
JOIN responses r ON r.userid = u.userid
JOIN questions q ON q.questionID = r.questionID
WHERE u.userID = @user
ORDER BY q.questionID
IF @string <> '' SELECT @string = LEFT(@string, LEN(@string)-1)
RETURN @string
END
--call function
SELECT userid, dbo.get_responses(userid)
FROM users
--cleanup
drop table users
drop table questions
drop table responses
--results of the above function call
1 U1 resp to Q1, U1 resp to Q2, U1 resp to Q3
2 U2 resp to Q1, U2 resp to Q2
3 U3 resp to Q1, U3 resp to Q2, U3 resp to Q3
4 U4 resp to Q2
"ORDER BY" in the function tells in which order the responses will be concatenated - in this case it is based on question number. Mark that with order by clause, you'll get consistent results; without it, ordering in concatenated string is unpredictable.
HTH, Vladan
-
CREATE TABLE #temp (userid int)
INSERT INTO #temp (userid) SELECT userid from [users]
DECLARE @maxq int, @qid int,@sql nvarchar(4000)
SELECT @maxq = MAX(questionid) FROM [questions]
SET @qid = 1
WHILE @qid <= @maxq
BEGIN
SET @sql = 'ALTER TABLE #temp ADD question' + cast(@qid as varchar) + ' varchar(20)'
EXEC(@sql)
SET @sql = 'UPDATE t SET t.question' +
cast(@qid as varchar) +
' = r.response FROM #temp t INNER JOIN [responses] r ON r.userid = t.userid AND questionid = ' +
cast(@qid as varchar)
EXEC(@sql)
SET @qid = @qid + 1
END
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
Ok, well we are REALLY getting close here. Thanks so much for everyones help. The issue now is that I wasn't completely honest before about the table structure because I thought I could figure it out with the simple example (but Its not looking good).
Two issues exist with the above loop.
1. This uses GUIDS and not autonumbers, am I just going to have to make it use autonumbers? In other words I could still select the count(*) instead of max auto number but that doesn't do my much good later in the query.
2. The question name is not 'question 1'. In other words, for each question I need somethink like (select questionname from questions where QuestionID = Current GUID in Loop). This kind of goes along with #1 since I don't have access to this GUID.
Any ideas?
-
Also, here is what my query looks like when I replace the actual column names and table names. The real issue is that these are QUIDS and not integers.
CREATE TABLE #temp (userid int)
INSERT INTO #temp (userid) SELECT userid from [users]
DECLARE @maxq int, @qid int,@sql nvarchar(4000)
SELECT @maxq = count(*) FROM [dynamicregistration_question]
SET @qid = 1
WHILE @qid <= @maxq
BEGIN
SET @sql = 'ALTER TABLE #temp ADD question' + cast(@qid as varchar) + ' varchar(20)'
EXEC(@sql)
SET @sql = 'UPDATE t SET t.question' +
cast(@qid as varchar) +
' = r.response FROM #temp t INNER JOIN [dynamicregistration_questionresponse] r ON r.userid = t.userid AND DynamicQuestionID = ' +
cast(@qid as varchar)
EXEC(@sql)
SET @qid = @qid + 1
END
SELECT * FROM #temp
DROP TABLE #temp
Obviously since I am using the count and trying to join each integer to a GUID this doesn't work, but you get the idea. Currently I get "Operand type clash: uniqueidentifier is incompatible with tinyint". This would be easy if I used autonumbers but this is a little late in the design for that!
-
Hello Chad,
did you try the code I posted? I think it should work fine, no matter whether you are using GUID or autonumber as ID. It simply makes JOIN on the ID. Please let me know what problems you have with it and I will try to find a way to deal with them.
Oh, now I look at your previous posts again, maybe you didn't want to concatenate the answers but leave them as separate columns, and put the name of question as column name? Sorry, this wasn't apparent to me until I read David's solution... If it is so, then you'll have to use some dynamic code, because the number of columns and their names can be different every time. Didn't check David's solution, but on first glance it looks like it should do what you need. About the GUID - couldn't you create temporary table with identity column and the GUID, insert all pertinent question GUIDs into this temp table, then loop through the identity column values and always store the respective GUID in some parameter, then use this parameter to find question name and other things you need?
-
Do as Vladan suggests (he always seems to get the best ideas
)or select the guids into a temp table and
select top 1 guid into a variable
use it instead of the id in my query
delete the entry from the temp table using the variable
loop until table is empty
Far away is close at hand in the images of elsewhere.
Anon. -
First, you guys rock! Thank you very much... Here is what it ended up being.
INSERT INTO #MyQuestions (DynamicID)
SELECT dynamicquestionID
FROM dynamicregistration_question where ModuleID = 402
CREATE TABLE #temp (userid int)
INSERT INTO #temp (userid) SELECT userid from [users]
DECLARE @maxq int, @qid int,@sql nvarchar(4000)
SELECT @maxq = count(*) FROM [#MyQuestions]
SET @qid = 1
WHILE @qid <= @maxq
BEGIN
DECLARE @QuestionName varchar(100)
DECLARE @DynamicQID uniqueidentifier
Set @QuestionName = (Select Question from dynamicregistration_question where DynamicQuestionID = (select DynamicID from #MyQuestions where ID = @qid))
Set @DynamicQID = (select DynamicID from #MyQuestions where ID = @qid)
--select @QuestionName, @DynamicQID
SET @sql = 'ALTER TABLE #temp ADD [' + @QuestionName + '] varchar(500)'
EXEC(@sql)
SET @sql = 'UPDATE t SET t.[' + @QuestionName + '] = r.response FROM #temp t INNER JOIN [dynamicregistration_questionresponse] r ON r.userid = t.userid AND r.DynamicQuestionID = ''' + Cast(@DynamicQID as varchar(100)) + ''''
EXEC(@sql)
--select @sql
SET @qid = @qid + 1
END
SELECT U.UserName AS [DynamicUserName], Z.* FROM #temp Z
Inner Join Users U on Z.UserID = U.UserID
Order By U.UserNAme Asc
DROP TABLE #temp
GO
DROP TABLE #MyQuestions
GO
-
ONe more question? I loaded this into a stored procedure and noticed that it drops my drop statements (even if table exists drop statments)
-
Probably you didn't take out the GO commands... CREATE PROCEDURE statement ends as soon as it reaches the first GO, everything following it will not be included in the proc and treated as separate piece of code (will be run immediately). It is not the DROP which causes this behavior, if you replace it with SELECT, it will be missing from the procedure as well.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply