Re: Re: Query from Hell (for me anyway)

Thank You Hugh !!!! You examples put me on the right track and I did manage to get it working. I've taken notes about the normalization problem, and when I get a chance I'll re-design the db into a more friendly version :-) Again Many Thanks ! Hugh Bothwell wrote: > > "Chris Mackenzie" <cmackenz@optushome.com.au> wrote in message > news:3CFA17B7.79886B57@optushome.com.au... > > 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 -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?"