Sub query help needed

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

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.