SQL Help Please

[Hal Robertson]

Hello,

I would like to model and implement a simple application for keeping
track of user survey data for our marketing department.

The concept is very simple: a user of our website can opt-in to answer
a short list of questions for data collection.

The answers to all of the questions are presented in drop-down boxes,
so no free form text is stored.

We would like to store all of the questions, answer options, and user
answers in the database.

I have three simple tables to present (all fields are NOT NULL)

questions
--------------
question_id int(11)
question_text varchar(255)

answer_options
--------------
answer_option_id int(11)
question_id int(11)
answer_option_text varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_id int(11)
user_id int(11)

What I'm scratching my brain trying to solve is how to construct the
appropriate SQL statement to, in one query, select all questions, all
answers, and those answers that a specific user_id has already
selected.

Something akin to:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM questions q, answer_options ao, user_answers ua
WHERE ????

The end result would have a not null user_answer_id where the user has
made a choice.

I have been trying with LEFT OUTER JOINS, and am thinking that a
subquery of user_answers by user_id into a temp table might be needed,
though I've never attempted one first.

Any generous folk out there mind posting up some SQL that would work?

many thanks for the help!

cheers!

Hal

[Hal Robertson]

How about:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM
questions q,
answer_options ao
LEFT JOIN
user_answers ua
ON
${user_id}=ua.user_id AND ao.answer_option_id=ua.answer_option_id
WHERE
ao.question_id=q.question_id
ORDER BY
q.question_id, ao.answer_option_id

Appears to work. Any comments on improvements?

ha***********@yahoo.com (Hal Robertson) wrote in message news:<a8*************************@posting.google.c om>...

Hello,

I would like to model and implement a simple application for keeping
track of user survey data for our marketing department.

The concept is very simple: a user of our website can opt-in to answer
a short list of questions for data collection.

The answers to all of the questions are presented in drop-down boxes,
so no free form text is stored.

We would like to store all of the questions, answer options, and user
answers in the database.

I have three simple tables to present (all fields are NOT NULL)

questions
--------------
question_id int(11)
question_text varchar(255)

answer_options
--------------
answer_option_id int(11)
question_id int(11)
answer_option_text varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_id int(11)
user_id int(11)

What I'm scratching my brain trying to solve is how to construct the
appropriate SQL statement to, in one query, select all questions, all
answers, and those answers that a specific user_id has already
selected.

Something akin to:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM questions q, answer_options ao, user_answers ua
WHERE ????

The end result would have a not null user_answer_id where the user has
made a choice.

I have been trying with LEFT OUTER JOINS, and am thinking that a
subquery of user_answers by user_id into a temp table might be needed,
though I've never attempted one first.

Any generous folk out there mind posting up some SQL that would work?

many thanks for the help!

cheers!

Hal

[Hal Robertson]

How about:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM
questions q,
answer_options ao
LEFT JOIN
user_answers ua
ON
${user_id}=ua.user_id AND ao.answer_option_id=ua.answer_option_id
WHERE
ao.question_id=q.question_id
ORDER BY
q.question_id, ao.answer_option_id

Appears to work. Any comments on improvements?

ha***********@yahoo.com (Hal Robertson) wrote in message news:<a8*************************@posting.google.c om>...

Hello,

I would like to model and implement a simple application for keeping
track of user survey data for our marketing department.

The concept is very simple: a user of our website can opt-in to answer
a short list of questions for data collection.

The answers to all of the questions are presented in drop-down boxes,
so no free form text is stored.

We would like to store all of the questions, answer options, and user
answers in the database.

I have three simple tables to present (all fields are NOT NULL)

questions
--------------
question_id int(11)
question_text varchar(255)

answer_options
--------------
answer_option_id int(11)
question_id int(11)
answer_option_text varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_id int(11)
user_id int(11)

What I'm scratching my brain trying to solve is how to construct the
appropriate SQL statement to, in one query, select all questions, all
answers, and those answers that a specific user_id has already
selected.

Something akin to:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM questions q, answer_options ao, user_answers ua
WHERE ????

The end result would have a not null user_answer_id where the user has
made a choice.

I have been trying with LEFT OUTER JOINS, and am thinking that a
subquery of user_answers by user_id into a temp table might be needed,
though I've never attempted one first.

Any generous folk out there mind posting up some SQL that would work?

many thanks for the help!

cheers!

Hal

[Hal Robertson]

How about:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM
questions q,
answer_options ao
LEFT JOIN
user_answers ua
ON
${user_id}=ua.user_id AND ao.answer_option_id=ua.answer_option_id
WHERE
ao.question_id=q.question_id
ORDER BY
q.question_id, ao.answer_option_id

Appears to work. Any comments on improvements?

ha***********@yahoo.com (Hal Robertson) wrote in message news:<a8*************************@posting.google.c om>...

Hello,

I would like to model and implement a simple application for keeping
track of user survey data for our marketing department.

The concept is very simple: a user of our website can opt-in to answer
a short list of questions for data collection.

The answers to all of the questions are presented in drop-down boxes,
so no free form text is stored.

We would like to store all of the questions, answer options, and user
answers in the database.

I have three simple tables to present (all fields are NOT NULL)

questions
--------------
question_id int(11)
question_text varchar(255)

answer_options
--------------
answer_option_id int(11)
question_id int(11)
answer_option_text varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_id int(11)
user_id int(11)

What I'm scratching my brain trying to solve is how to construct the
appropriate SQL statement to, in one query, select all questions, all
answers, and those answers that a specific user_id has already
selected.

Something akin to:

SELECT q.question_id,
q.question_text,
ao.answer_option_text,
ao.answer_option_id,
ua.user_answer_id
FROM questions q, answer_options ao, user_answers ua
WHERE ????

The end result would have a not null user_answer_id where the user has
made a choice.

I have been trying with LEFT OUTER JOINS, and am thinking that a
subquery of user_answers by user_id into a temp table might be needed,
though I've never attempted one first.

Any generous folk out there mind posting up some SQL that would work?

many thanks for the help!

cheers!

Hal