Use a LEFT JOIN
and check for null values. Something like:
SELECT profile.* FROM user_profile profile
LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL
Here's a link to the various joins
Here is your original query
SELECT
`school`.`ARCO_name`,
`student-`.`ClassSize_7`,
`student-`.`ClassSize_8`,
`degree_o`.`degree_code`,
`accredit`.`full_faculty_3`,
`accredit`.`total_faculty_3`,
`accredit`.`pc_terminal`,
`accredit`.`stud_fac_ratio`
FROM school
INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;
Two Suggestions
- Perform
LIMIT 0, 25
on school table before the INNER JOINs
- INNER JOIN
school.scid
to the other three(3) tables
Here is my propsed query
SELECT
`school`.`ARCO_name`,
`student-`.`ClassSize_7`,
`student-`.`ClassSize_8`,
`degree_o`.`degree_code`,
`accredit`.`full_faculty_3`,
`accredit`.`total_faculty_3`,
`accredit`.`pc_terminal`,
`accredit`.`stud_fac_ratio`
FROM
(SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
INNER JOIN `degree_o` ON `school`.`scid` = `degree_o`.`scid`
INNER JOIN `accredit` ON `school`.`scid` = `accredit`.`scid`
;
Give it a Try !!!
UPDATE 2012-06-25 12:13 EDT
To speed up the subquery, make sure you have this index:
ALTER TABLE school ADD INDEX ARCO_name_scid_ndx (ARCO_name,scid);
UPDATE 2012-06-25 12:18 EDT
AS pointed out by @yercube in his comments to my answer, you may want to attach the student-id from to degree_o and degree_id over to accedit. Perhaps something like this:
SELECT
`school`.`ARCO_name`,
`student-`.`ClassSize_7`,
`student-`.`ClassSize_8`,
`degree_o`.`degree_code`,
`accredit`.`full_faculty_3`,
`accredit`.`total_faculty_3`,
`accredit`.`pc_terminal`,
`accredit`.`stud_fac_ratio`
FROM
(SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
INNER JOIN `degree_o` ON `student-`.`studend_id` = `degree_o`.`studend_id`
INNER JOIN `accredit` ON `degree_o`.`degree_id` = `accredit`.`degree_id`
;
Best Answer
It's more or less like this...