Query Help - Finding unused values

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)

Query Help - Finding unused values

Author

Topic

morleyz
Starting Member

17 Posts

Posted - 2010-11-24 : 10:10:23

We have a survey system that allows users to build surveys and send them out to groups of users by e-mail. The process of setting up a survey is to create questions, then organize the questions into a template (order and layout of the questions) that gets assigned to a survey group (start date, end date, recipients, etc.) As the system has grown, I need a way to determine questions that aren't used in any survey group.

Relevant data structure:
questions_table: fields: id, question

templates_table: fields: id, template_name

template_match_table: fields: template_id (from templates_table), question_id (from questions_table), template_order

survey_group_table: id, group_name, template_id (from templates_table), start_date, end_date

The question I need to answer is: What questions are not used by any survey groups?

I am able to query for questions that aren't in any templates. I can also query for templates that aren't in use by any survey groups, but what happens over time is that as the groups are purged, the questions and templates hang around and the way our system works I really need a way to find the questions that aren't in any groups and I just am not sure where to start.

Any help would be greatly appreciated.

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 10:30:27

select q.*
from questions q
left join templates_match tm
on tm.question_id = q.question_id
left join survey_group sg
on sg.template_id = tm.template_id
where sg.template_id is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

morleyz
Starting Member

17 Posts

Posted - 2010-11-24 : 14:51:01

I think this is very close, but it is returning some values it shouldn't. It looks like if a question belongs to a template that doesn't have survey group assigned, that question is returned even if that question is in a different template and group combination.

It is possible for questions to be used in multiple templates and multiple templates to be used in multiple survey groups.

I tried tweaking it different ways and still got the same results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources