Comparing a column list split to a table.

[twdo]

Let me see if I can explain my situation clearly.

I have a table with the columns:

answer_id, question_id, member_id, answer

- answer_id is the primary key for the table.
- question_id relates to another table with questions for a user. The
table holds the question and the possible choices in a varchar field
separated by a delimiter.
- member_id is self-explanatory
- answer is a varchar field of all the choices the user selected,
separated by a delimiter.

Here is my problem.

I am trying to search all members that have answered, say, question_id
= 2 where they selected 'brown' as one of their choices.

i can do this if they selected ONLY that item, but not multiple items.

The problem is this portion

answer in
(select valu from dbo.iter_intlis t.....

I need this to be something like....

function_to_ret urn_all_separat ed_answers(answ er) in
(select valu from dbo.iter_intlis t

The current way, it is only returning members that have an answer
'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,
what I need to do is separate the list of answers and say :

select member_id from profile_answers where

ANY ANSWER in function_to_spl it(answer) MATCHES ANY OF THESE (select
valu from dbo.iter_intlis t...

It seems I might have to join or something, I am just a little lost
right now.

Here is my proc.

ALTER procedure search_detailed _get_ids

@question_id as integer,
@answers as varchar(8000),
@member_ids ntext

as

declare @v as varchar(8000)

--get the delimited string of all possible answers
set @v = (select bind_data from profiles_questi ons where question_id =
@question_id)

--prepare it for the function only accepting 1 char
set @v = replace(@v, '||', '|')

--gimme all members that match
select member_id from profiles_answer s where question_id = @question_id
and answer in
(select valu from dbo.iter_intlis t_to_table(@v, '|') where listpos in

(select valu from dbo.iter_intlis t_to_table(@ans wers, ',')))
and member_id in (select valu from dbo.iter_intlis t_to_table
(@member_ids, ','))

return
go

[Erland Sommarskog]

Re: Comparing a column list split to a table.

twdo (johnj@tampaweb development.com ) writes:[color=blue]
> Let me see if I can explain my situation clearly.
>
> I have a table with the columns:
>
> answer_id, question_id, member_id, answer
>
> - answer_id is the primary key for the table.
> - question_id relates to another table with questions for a user. The
> table holds the question and the possible choices in a varchar field
> separated by a delimiter.
> - member_id is self-explanatory
> - answer is a varchar field of all the choices the user selected,
> separated by a delimiter.[/color]

Redo the table design, and move answer to a subtable:

CREATE TABLE answers (answer_id int NOT NULL,
answer varchar(10) NOT NULL,
CONSTRAINT pk_answers PRIMARY KEY (answer_id, answer)

Do the same for the answers to the questions.

If you are really stuck with the design, use temp tables in the
procedure. But note that with the design above it is not possible to
answer Brown more than once to a question - which presumably is a
good thing.

Working with comma-separated lists is really painful in SQL, because
relation algebra - in which SQL does take its foundation - assumes
that values are atomic, and yours aren't.

I can not really write a query for you with these tables, since I
couldn't make out whether Brown had to be a correct answer, or if
you just wanted any one who had answered Brown.

And, oh, the usual advice apply:

o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.

That increases your does for a tested solution in reposnse.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[twdo]

Re: Comparing a column list split to a table.

> o CREATE TABLE statements for the tables involved.[color=blue]
> o INSERT statements with sample data.
> o The desired result given the sample.
>
> That increases your does for a tested solution in reposnse.
>[/color]

I am stuck with the design of the tables. I can probably do some
looping to achieve what I need but I wanted to avoid loops at all costs
because of the performance.

Below is some table and data code. Basically, if you run the procedure
first provided, it will work if someone answers only ONE of the
possible choices (question_id = 1) BUT, if someone answers multiple
items, it will fail. I need it to split the list of answers and split
the list of possible choices and see if *ANY* match at all. Does that
make sense? I appreciate all help.

CREATE TABLE [dbo].[profiles_answer s] (
[answer_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_id] [int] NOT NULL ,
[member_id] [int] NOT NULL ,
[answer] [varchar] (7000) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[profiles_questi ons] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[group_id] [int] NOT NULL ,
[display_label] [varchar] (50) NOT NULL ,
[field_type_id] [int] NOT NULL ,
[bind_data] [varchar] (7000) NOT NULL ,
[display_order] [int] NOT NULL ,
[status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[profiles_questi ons]
(group_id, display_label, field_type_id, bind_data, display_order,
status)
VALUES
(1, 'Interests', 1, 'Computers||Out doors', 1, 1)
INSERT INTO [dbo].[profiles_questi ons]
(group_id, display_label, field_type_id, bind_data, display_order,
status)
VALUES
(1, 'Hair Color', 1, 'Brown||Black|| Blonde', 1, 1)

INSERT INTO [dbo].[profiles_answer s]
(question_id, member_id, answer)
VALUES
(2, 1, 'Brown')

INSERT INTO [dbo].[profiles_answer s]
(question_id, member_id, answer)
VALUES
(1, 1, 'Computers, Outdoors')

[Erland Sommarskog]

Re: Comparing a column list split to a table.

twdo (johnj@tampaweb development.com ) writes:[color=blue]
> I am stuck with the design of the tables. I can probably do some
> looping to achieve what I need but I wanted to avoid loops at all costs
> because of the performance.[/color]

If you feel that you cannot change that design, don't even consider
performance. Perfomance is not achievable with that design.
[color=blue]
> Below is some table and data code. Basically, if you run the procedure
> first provided, it will work if someone answers only ONE of the
> possible choices (question_id = 1) BUT, if someone answers multiple
> items, it will fail. I need it to split the list of answers and split
> the list of possible choices and see if *ANY* match at all. Does that
> make sense? I appreciate all help.[/color]

Thanks for the tables and insert data. But it would help a lot if you
gave different examples of input parameters, and what result you expect.
You refer to your procedure, but it cannot even run on the test data,
since it uses iter_intlist_to _table, and the answers are character...

Anyway, I don't really like guessing, since I may be wasting my time
on the wrong guess.

One hint is that instead of IN, use EXISTS instead. That's a more
powerful operator, with fewer gotchas, and often better performance.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp