"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