Since I said I'd post a follow up (and I have worked it out after a coffee) using a LEFT OUTER JOIN I shall.<div><br></div><div><div><font class="Apple-style-span" face="'courier new', monospace">SELECT u.*</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace">FROM usr AS u</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> LEFT OUTER JOIN mahara.group_member AS gm</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> ON <a href="http://u.id">u.id</a> = gm.member</font></div><div><font class="Apple-style-span" face="'courier new', monospace">WHERE gm.member IS NULL</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace">OR</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> (</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> gm.member IS NOT NULL</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> AND gm.group != 10</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> );</font></div>
<div><br></div><div>Working on the same principal.</div><div>We want every row from the usr table</div><div>That doesn't have an entry in <span class="Apple-style-span" style="font-family: 'courier new', monospace; ">group_member </span>(so no member)</div>
<div>Unless that entry has a <font class="Apple-style-span" face="'courier new', monospace">group</font> that isn't '10'</div><div><br></div><div>So you could also use:</div><div><div><font class="Apple-style-span" face="'courier new', monospace">SELECT u.*</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace">FROM usr AS u</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> LEFT OUTER JOIN mahara.group_member AS gm</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> ON <a href="http://u.id">u.id</a> = gm.member</font></div><div><font class="Apple-style-span" face="'courier new', monospace">WHERE gm.group IS NULL</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace">OR gm.group != 10</font></div><div><font class="Apple-style-span" face="'courier new', monospace">;</font></div></div><div><br></div>
<div>NOT IN statements are best avoided unless you have a small list of predefined items for performance purposes.</div><div><br></div><div class="gmail_quote">On 29 June 2011 10:07, Dan Attwood <span dir="ltr"><<a href="mailto:danattwood@gmail.com">danattwood@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div class="gmail_quote"><div class="im"><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF"><div>Have you tested the subquery in php?<br></div></div></blockquote>
<div><br></div></div><div>I have and that's where it wasn't working. </div><div>However I have now found the reason and it was because I'm a total idiot. Next time i'll double check i'm look at the right database D'oh</div>
</div>
<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" target="_blank">https://mailman.lug.org.uk/mailman/listinfo/kent</a><br></blockquote></div><br></div>