Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    Sub query help needed

    Hi All,

    I currently have a query that looks like so;

    Code:
    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;

    Code:
    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
    Code:
    "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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •