Help with view - is this possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Help with view - is this possible?

Author

Topic

telemachus
Starting Member

2 Posts

Posted - 2004-11-18 : 14:57:55

Okay, I've got a table of a survey that holds one record for every user question response, like below.

AnswerID   UserID   Question        Answer
-------------------------------------------
100        10       q01_a           213    
101        10       q01_b           32
102        10       q01_locked      1
103        10       q02_1_a           56
104        10       q02_1_b           118
105        10       q02_1_c           67
106        27       q02_1_a           400
107        27       q02_1_b           0
108        27       q02_1_c           81
109        27       q02_1_locked      19

Records with "_locked" in the question field indicate that a user has submitted data for a particular section. What I'm trying to do is create a view of all locked data. In other words, when I find the record with "q01_locked," I want all of that user's records with "q01_*" - and so on.

I can get a list of locked sections with something like this:

SELECT UserID, REPLACE(Question, '_locked', '') AS questionBase
FROM tblAnswers
WHERE (question LIKE '%_locked')

but then I would need to use a LIKE operator over each of the records from the subquery, correct?

Any ideas?

Many thanks,
James

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-18 : 15:06:26

Will this work??

SELECT AnswerID, UserID, Question, Answer
FROM tblAnswers INNER JOIN
(SELECT UserID, REPLACE(Question, '_locked', '') AS questionBase
FROM tblAnswers
WHERE (question LIKE '%_locked')) a ON (tblAnswers.UserID = a.UserID AND tblAnswers.Question LIKE a.questionBase + '%')

Dustin Michaels

telemachus
Starting Member

2 Posts

Posted - 2004-11-18 : 15:15:08

Great!

Thanks Dustin, that seems to do the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources