multiple select question - 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)

multiple select question

Author

Topic

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 05:14:16

Hi - I have an application where users dynamically create questions - so I have a questions table:

questionID | question
---------------------
1 | Age
2 | Height
3 | Name
4 | Adress

and so on. Then I have another table - answers - these hold the answers that users have given to the questions so it looks something like:

id | questionID | UserID | Answer
---------------------------------
1 | 1 | 001 | 34
2 | 2 | 001 | 6
3 | 3 | 001 | Bob
4 | 4 | 001 | 1 street
5 | 1 | 002 | 23
6 | 2 | 002 | 5.4

And so on.

Lets say I now need to select all users who are 34 years called bob - from the above table, you can see I'd need to select WHERE QuestionID=1 AND ANSWER=34 which would give me 1 row and then I'd also need to select WHERE QuestionID=3 AND ANSWER=Bob

But you can see that I would get 2 rows there (if I did the 2 selects seperately).

What I dont understand is how to query that table and get a list of users who meet both criteria as the results are on different rows.

Do you see what I mean? It would be easy if I had the questions hard-coded into table columns, then I would have 1 row of answers per user but, as I am dynamically creating the questions, the answers are spread over multiple rows.

Any ideas?

Thanks.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 05:23:22

You can use a OR clause
WHERE (QuestionID=1 AND ANSWER=34)OR(QuestionID=3 AND ANSWER=Bob)

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 05:28:22

Thanks - but, if I'm querying it where I want to match BOTH criteria, I need to select WHERE (QuestionID=1 AND ANSWER=34)AND(QuestionID=3 AND ANSWER=Bob) but that doesn't make sense becasue no row will match all those criteria.

I dont just want to return all users called bob and then all users who are 34, I only want to return users who are 34 AND Called bob.

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 05:50:23

It's almost like I need to somehow create a view/temp table that is generated from the reults - so I would have a table that looks like this:

userID | Age | Height | Name | Address
---------------------------------------
001 | 34 | 6 | Bob | 1 street

Then I could query that table..

But this table's columns need to be generated from the 'questions' table data and then be populated from the 'answers' table.

See what I'm trying to achieve?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 06:00:31

Select
max(case when q.questionID=1 then a.answer end)as Age,
max(case when q.questionID=2 then a.answer end)as Height,
max(case when q.questionID=3 then a.answer end)as Name,
max(case when q.questionID=4 then a.answer end)as Address,
from question q inner join Answer a on q.questionID=a.questionID
Where (Here goes your filter criteria)

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 06:03:23

Thanks - but I dont know what the columns will be - the values in the question table can change so I would need to dynamically create the above statement based on the values in the questions table - not hard code them in.

Any idea how to do that?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-28 : 06:10:37

[code]-- *** Test Data ***
DECLARE @t TABLE
(
[id] int NOT NULL
,questionID int NOT NULL
,UserId int NOT NULL
,Answer varchar(50) NOT NULL
)
INSERT INTO @t
SELECT 1, 1, 1, '34' UNION ALL
SELECT 2, 2, 1, '6' UNION ALL
SELECT 3, 3, 1, 'Bob' UNION ALL
SELECT 4, 4, 1, '1 street' UNION ALL
SELECT 5, 1, 2, '23' UNION ALL
SELECT 6, 2, 2, '5.4'
-- *** End Test Data ***

SELECT UserID
FROM @t
WHERE (questionID = 1 AND Answer = '34')
OR (questionID = 3 AND Answer = 'Bob')
GROUP BY UserID
HAVING COUNT(*) = 2
[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 06:11:45

Please try to post your full requirements at the first.

You will have to use dynamic pivot.See this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 07:12:50

OK - here's the actual table (well, its actually a view but that doesn't matter): http://www.johnloydall.co.uk/TABLE.jpg

And here's my pivot code:

DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(FieldName as varchar) + ']',
'[' + cast(FieldName as varchar)+ ']')
FROM vwResultsWithNames
Group by FieldName

DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM vwResultsWithNames
PIVOT
(
MAX(FieldContent)
FOR [FieldName]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

It almost works - it correctly creates the columns and populates the table but - it creates a new row per column value - so rather than display a users results all on 1 row, it creates a new row per answer meaning the rest of the fields in that row are NULL.

It's nearly there - I just need to have each users answers on each row.

BTW - in the actual table 'FieldContent' is the answer, PrimaryKeyValue is the UserID, FieldID is the id of the question and obviously FieldName is the quesation itslef.

Thanks

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 07:20:12

Can u pls show the resultset of what you are getting & also the query that is dynamically getting generated.You can use Print(@query)to post the query.

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 07:32:53

SELECT *
FROM vwResultsWithNames
PIVOT
(
MAX(FieldContent)
FOR [FieldName]
IN ([address1],[address2],[Background],[city],[company],[CompanyAddress],[CompanyDescription],[CompanyName],[country],[County],[CurrentEmployment ],[Expertise],[firstName],[KeyVerticals ],[lastName],[MemberCountry],[mobileNumber],[phone],[position],[postalCode],[reference],[Sector],[SizeOrganisation],[state],[title],[WebAddress])
)
AS p

And the results look like this:

http://www.johnloydall.co.uk/results.jpg

See how, for example, 'we sell computers' is on a different row to 'John's computers' - they are answers by the same user (Primarykeyvalue is 2104).
I need all the answers from one user (PrimaryKeyValue) to appear on 1 row.

Thanks

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 07:43:31

Get rid of ValueId & FieldId in the select statement of the above query.

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 08:36:35

Awesome! Thanks - that's a massive help

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 09:16:08

OK - So now I have those results in a nice grid, how do I query those results? How do I select from those results - I can't put that into a view can I?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-28 : 09:22:21

How about a different approcah?

You can use EXISTS. It will probably be quicker.

Example


/*
questionID | question
---------------------
1 | Age
2 | Height
3 | Name
4 | Adress
*/
DECLARE @question TABLE (
	[ID] INT
	, [question] NVARCHAR(255)
	)
INSERT @question
      SELECT 1, 'Age?'
UNION SELECT 2, 'Height?'
UNION SELECT 3, 'Name?'
UNION SELECT 4, 'Address?'

/*
id | questionID | UserID | Answer
---------------------------------
1 | 1 | 001 | 34
2 | 2 | 001 | 6
3 | 3 | 001 | Bob
4 | 4 | 001 | 1 street
5 | 1 | 002 | 23
6 | 2 | 002 | 5.4
*/
DECLARE @answer TABLE (
	[Id] INT
	, [questionId] INT
	, [userID] INT
	, [answer] NVARCHAR(255)
	)
INSERT @answer
      SELECT 1, 1, 101, '34'
UNION SELECT 2, 2, 101, '6'
UNION SELECT 3, 3, 101, 'Bob'
UNION SELECT 4, 4, 101, '1 Street'
UNION SELECT 5, 1, 102, '23'
UNION SELECT 6, 2, 102, '5.4'

-- Where name? = Bob and Age = 34
SELECT
	[userID]
FROM
	@answer a
WHERE
	a.[answer] = '34'
	AND a.[questionID] = 1
	AND EXISTS (
		SELECT
			1
		FROM
			@answer b
		WHERE
			b.[questionID] = 3
			AND b.[answer] = 'Bob'
			AND b.[userId] = a.[userID]
		)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-28 : 09:26:30

No charlie he wanted the o/p lk this

userID | Age | Height | Name | Address
---------------------------------------
001 | 34 | 6 | Bob | 1 street

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-28 : 09:36:47

Yeah the pivot table would be a great approach if it wasn't dynamic. I think he actually wants a framework to query this data.

quote:
Lets say I now need to select all users who are 34 years called bob - from the above table, you can see I'd need to select WHERE QuestionID=1 AND ANSWER=34 which would give me 1 row and then I'd also need to select WHERE QuestionID=3 AND ANSWER=Bob

But you can see that I would get 2 rows there (if I did the 2 selects seperately).

What I dont understand is how to query that table and get a list of users who meet both criteria as the results are on different rows.

Do you see what I mean? It would be easy if I had the questions hard-coded into table columns, then I would have 1 row of answers per user but, as I am dynamically creating the questions, the answers are spread over multiple rows.

Now I agree that the pivot table would *definitely* be the way to go if you wanted a final report but OP doesn't want that. He wants a framework that he can query. Because it's going to be a dynamic list of questions you can't create a view easily to do this.

However, using EXISTS he can write queries for any number of AND / OR conditions for his format of data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 10:25:08

Can I do this when I don't know what questions will exist in the questions table? It seems to me that the pivot table at least displays the data in a way that I want - I just now need to query that data. Your code looks like it has hard coded values in which I will never know.

Thanks.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-28 : 10:40:34

Pivot table = DISPLAY. Display = look at. Look at = front end.

Framework = Nice set of data. Nice set of data = QUERY ME.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 10:45:36

thanks - excuse my less than expert knowledge here but I dont understand how to use the 'EXISTS' example above when I don't know what the data/column names will be?

This bit:

INSERT @question
SELECT 1, 'Age?'
UNION SELECT 2, 'Height?'
UNION SELECT 3, 'Name?'
UNION SELECT 4, 'Address?'

I don't know that it will be Age, Height etc.. It can be any number of random values at runtime.

And also:

INSERT @answer
SELECT 1, 1, 101, '34'
UNION SELECT 2, 2, 101, '6'
UNION SELECT 3, 3, 101, 'Bob'
UNION SELECT 4, 4, 101, '1 Street'
UNION SELECT 5, 1, 102, '23'
UNION SELECT 6, 2, 102, '5.4'

How do I build this dynamically?

Thanks

loydall
Starting Member

33 Posts

Posted - 2009-07-28 : 10:56:12

Sorry - think I missed your point there.

Anyway - just to complicate things further, the WHERE clause of my select will have any number of criteria. So it could be:

WHERE Answer = "bob" and FieldId=1

or it could be:

WHERE Answer = "bob" and FieldId=1 AND Answer = "34" and FieldID="2"

And so on - I know the above syntax is wrong but you get the idea

My other approach is:

Use the pivot table - put it in a stored proc and then call it, returning all values.

Load the results into a .net DataTbale and then filter that table based on my criteria - but that means I always pull ALL records from my table - that will get heavy..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources