how to get needed data from mysql tables

Posted by I Like PHP on Stack Overflow See other posts from Stack Overflow or by I Like PHP
Published on 2010-03-29T13:29:53Z Indexed on 2010/03/29 13:33 UTC
Read the original article Hit count: 199

Filed under:

i have 3 tables

tbl_faculty(f_id,f_school)
tbl_school(s_id,s_city_s_role)
tbl_city(c_id,c_name)

in one school there may be many teacher(s_role=2) can be appointed,

but would be appointed only one principle(s_role=1)

first we need to create a school, then we appoint faculty.

now i want to appoint principle( where teachers may b already appointed or may b not)

so i need all school_id and city name where there is no principle appointed yet

references in tables

    f_school--->s_id
    s_city---->c_id 

i used below query but, it show not the exact result i want

SELECT s_id,c_name FROM tbl_school
                LEFT JOIN tbl_city ON c_id=s_city
                LEFT JOIN tbl_faculty ON f_school=s_id
             WHERE s_role<>1";

© Stack Overflow or respective owner

Related posts about mysql