SELECT

[ec]

I dunno what's wrong with this

 

$query = "SELECT teacherid, position FROM teacher WHERE teacherid NOT IN "members" ";

 

 

I'm trying to get the query  to select teacherid and position from a table called teacher provided that the teacherid doesn't appear in the table members as well...

 

any ideas??

[cooldude832]

try

<?php
$q = "select teacher.teacherid as teacherid, teacher.position as position from `teacher` where teacher.teacherid NOT IN (select member.teacherid from `members`)";
?>

so long as teacherid is the foreign key in members

[ec]

that's it working...thanks!

[aschk]

I can only forsee 1 problem with the SQL provided. If the members table becomes very large the IN (SELECT...) part of the statement will become very slow, because you're selecting ALL the members from the table regardless of anything else.

 

Try:

SELECT t.teacherid as 'teacherid'
      ,t.position as 'position'
FROM teachers t 
LEFT JOIN members m ON t.teacherid = m.teacherid
WHERE m.teacherid IS NULL

 

This does a LEFT JOIN based on the id of the teacher (which i'm hoping is indexed at least), so theoretically you will ONLY ever pull 1 row from the members table.

[fenway]

try

<?php
$q = "select teacher.teacherid as teacherid, teacher.position as position from `teacher` where teacher.teacherid NOT IN (select member.teacherid from `members`)";
?>

so long as teacherid is the foreign key in members

Ugh... a subquery with NOT IN... definitely use aschk's code.