PDA

View Full Version : Sub query help needed



greens85
08-08-2011, 10:59 AM
Hi All,

I currently have a query that looks like so;


SELECT *
FROM availability_calendar
LEFT JOIN indivdual_teachers
ON indivdual_teachers.teacher_id = availability_calendar.candidate_id
LEFT JOIN favourite_teachers
ON indivdual_teachers.teacher_id = favourite_teachers.teacher_id
WHERE (1=0';

$query .= ' OR (day='.$day.' AND month='.$month.' AND year='.$year.' AND availability="Available")';

$query .= " AND LA = '$local_authority' AND payscale LIKE '$cost%' AND postcode LIKE '$postcode%'";

$query .= ') AND indivdual_teachers.teacher_id IN (SELECT teacher_id FROM teacher_work_preferences WHERE work_preferences IN ("'.implode('", "', $_POST['sector']).'")) GROUP BY candidate_id';

This produces a result of something like;


SELECT * FROM availability_calendar LEFT JOIN indivdual_teachers ON indivdual_teachers.teacher_id = availability_calendar.candidate_id LEFT JOIN favourite_teachers ON indivdual_teachers.teacher_id = favourite_teachers.teacher_id WHERE (1=0 OR (day=8 AND month=08 AND year=2011 AND availability="Available") OR (day=9 AND month=08 AND year=2011 AND availability="Available") AND LA = 'Gateshead' AND payscale LIKE '%' AND postcode LIKE '%') AND indivdual_teachers.teacher_id IN (SELECT teacher_id FROM teacher_work_preferences WHERE work_preferences IN ("Early Years")) GROUP BY candidate_id

However I need to add in "sub-select" as I only want to return teachers who have been listed as favourites;

My favourite_teachers tbl has a structure of;

id | school_id | teacher_id
--------------------------

So basically I'm looking to combine the statement

"SELECT * FROM favourite_teachers WHERE school_id = '$id'

Can someone with more sql experience advise what the best way to integrate this would be?

Many thanks,

Greens85

Fumigator
08-08-2011, 03:20 PM
You are already joining with favorite teachers... so.... make it an inner join instead of a left outer join...? Will that give you what you want? If you need to qualify by school id as you mentioned, put that in your where clause.