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_i d int(11)
question_id int(11)
answer_option_t ext varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_i d 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_optio n_text,
ao.answer_optio n_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_optio n_text,
ao.answer_optio n_id,
ua.user_answer_ id
FROM
questions q,
answer_options ao
LEFT JOIN
user_answers ua
ON
${user_id}=ua.u ser_id AND ao.answer_optio n_id=ua.answer_ option_id
WHERE
ao.question_id= q.question_id
ORDER BY
q.question_id, ao.answer_optio n_id

Appears to work. Any comments on improvements?

ha***********@y ahoo.com (Hal Robertson) wrote in message news:<a8******* *************** ***@posting.goo gle.com>...

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_i d int(11)
question_id int(11)
answer_option_t ext varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_i d 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_optio n_text,
ao.answer_optio n_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_optio n_text,
ao.answer_optio n_id,
ua.user_answer_ id
FROM
questions q,
answer_options ao
LEFT JOIN
user_answers ua
ON
${user_id}=ua.u ser_id AND ao.answer_optio n_id=ua.answer_ option_id
WHERE
ao.question_id= q.question_id
ORDER BY
q.question_id, ao.answer_optio n_id

Appears to work. Any comments on improvements?

ha***********@y ahoo.com (Hal Robertson) wrote in message news:<a8******* *************** ***@posting.goo gle.com>...

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_i d int(11)
question_id int(11)
answer_option_t ext varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_i d 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_optio n_text,
ao.answer_optio n_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_optio n_text,
ao.answer_optio n_id,
ua.user_answer_ id
FROM
questions q,
answer_options ao
LEFT JOIN
user_answers ua
ON
${user_id}=ua.u ser_id AND ao.answer_optio n_id=ua.answer_ option_id
WHERE
ao.question_id= q.question_id
ORDER BY
q.question_id, ao.answer_optio n_id

Appears to work. Any comments on improvements?

ha***********@y ahoo.com (Hal Robertson) wrote in message news:<a8******* *************** ***@posting.goo gle.com>...

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_i d int(11)
question_id int(11)
answer_option_t ext varchar(255)

user_answers
--------------
user_answer_id int(11)
question_id int(11)
answer_option_i d 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_optio n_text,
ao.answer_optio n_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