"Chris Mackenzie" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> I've been trying to solve this for hours now with no end in sight, I'm
> hoping that someone here might be able to help me.
>
> I've got four tables set out as shown below:
>
> tbl_student_details
> id student_id name study_mode
> -------------------------------------------
> 1 12 Joe Smith 1
> 2 123 Jane Smith 1
>
>
> tbl_study_mode
> id txt_study_mode
> --------------------------------
> 1 Full Time
> 2 Part Time
> 3 Online
>
>
> tbl_student_history_answers
> id question_id p_answer
> ----------------------------------------
> 1 19 Network Management
> 2 19 Web Site Production
> 3 19 Web Site Management
> 4 20 Network Management
> 5 20 Web Site Production
> 6 20 Web Site Management
>
> tbl_student_history_info
> id student_id question_id answer_id
> -------------------------------------------
> 1 12 19 1
> 2 12 20 6
> 3 123 19 5
> 4 123 20 6
>
> Basically I need to pull out student_id, name and course selected for
> all students who elected full time study - and their response to
> question 19 and 20
First, this has a normalization problem - "question-id" is
redundant in either tbl_student_history_info or
tbl_student_history_answers. I will assume it removed
from tbl_student_history_answers.
There are two ways of factoring this, depending
on exactly how you plan to use it.
If you will always only want questions 19 and 20,
you can hard-code those into a per-student query,
like so:
SELECT
tbl_student_details.student_id,
tbl_student_details.name,
tbl_q19.p_answer AS a19,
tbl_q20.p_answer AS a20
FROM
(((((
tbl_student_details
JOIN tbl_study_mode
ON tbl_student_details.study_mode=tbl_study_mode.id)
JOIN tbl_student_history_info AS q19_info
ON
tbl_student_details.id=tbl_student_history_info.student_id)
JOIN tbl_student_history_answers AS tbl_q19
ON q19_info.answer_id=tbl_q19.id)
JOIN tbl_student_history_info AS q20_info
ON
tbl_student_details.id=tbl_student_history_info.student_id)
JOIN tbl_student_history_answers AS tbl_q20
ON q20_info.answer_id=tbl_q20.id)
WHERE
tbl_study_mode.txt_study_mode='Full Time'
AND q19_info.question_id=19
AND q20_info.question_id=20
This is actually a poor way of doing things, but it
makes your final PHP code much simpler.
If you will ever want to use this query to look at
answers to a different number of questions - say,
questions 19, 20 and 21 - you would be better
to do a per-student/per-question query, like so:
SELECT
tbl_student_details.student_id,
tbl_student_details.name,
tbl_student_history_info.question_id,
tbl_student_history_answers.p_answer
FROM
(((
tbl_student_details
JOIN tbl_study_mode
ON tbl_student_details.study_mode=tbl_study_mode.id)
JOIN tbl_student_history_info
ON
tbl_student_details.id=tbl_student_history_info.student_id)
JOIN tbl_student_history_answers
ON
tbl_student_history_info.question_id=tbl_student_history_answers.id)
WHERE
tbl_study_mode.txt_study_mode='Full Time'
AND tbl_student_history_info.question_id IN ( 19, 20 )
ORDER BY
tbl_student_details.student_id ASC,
tbl_student_history_info.question_id ASC
This will return several records per student, one
for each question you want to know about; the ORDER
BY is useful to make all records for a single student
consecutive so your PHP code can process it easily.
Hope this helps.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
