Not 100% sure on the PHP side. I know that older versions of MySQL (and this could be what the PHP is thinking of) didn't have good sub query support. This could also be a case of what database you are connected to/have access to (I'm presuming that mahara is a different DB)<br>
<br><div>In some systems this LEFT OUTER JOIN method can be a lot more efficient than a NOT IN</div><div>You still get everything from table 1 that doesn't have an instance in table 2</div><div><br></div><div><div><font class="Apple-style-span" face="'courier new', monospace"><div>
SELECT u.*</div><div>FROM usr AS u</div><div> LEFT OUTER JOIN mahara.group_member AS gm</div><div> ON <a href="http://u.id">u.id</a> = gm.member</div><div>WHERE gm.member IS NULL;</div>
</font><br>This doesn't take care of the requirement on table 2 having a condition on it. I am going to play with some IF/CASE statements and get back to you.</div><div><br></div><div><br></div><div><br></div><div>On 29 June 2011 09:13, Dan Attwood <<a href="mailto:danattwood@gmail.com">danattwood@gmail.com</a>> wrote:<br>
> I know that isn't strictly Linux but here we go;<br>> I have the following mysql query:<br>> SELECT * FROM usr WHERE <a href="http://usr.id">usr.id</a> NOT IN (SELECT DISTINCT member FROM<br>> mahara.group_member WHERE mahara.group_member.group='10')<br>
> This selects all users in the usr table that don't have entry for group 10<br>> in the group_member table<br>> The query works as expected when run in mysql workbench<br>> However when I run it in a php script it only run the first part, so does a<br>
> select * from usr<br>> Has anyone come across this before and have any idea of a fix?<br>> Dan<br>> <br>> _______________________________________________<br>> Kent mailing list<br>> <a href="mailto:Kent@mailman.lug.org.uk">Kent@mailman.lug.org.uk</a><br>
> <a href="https://mailman.lug.org.uk/mailman/listinfo/kent">https://mailman.lug.org.uk/mailman/listinfo/kent</a><br>><br><br></div></div>