<br><br><div class="gmail_quote">On 29 June 2011 10:54, David Halliday <span dir="ltr"><<a href="mailto:david.halliday@gmail.com">david.halliday@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
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 class="im"><div><font face="'courier new', monospace">SELECT u.*</font></div>
<div><font face="'courier new', monospace">FROM usr AS u</font></div><div><font face="'courier new', monospace"> LEFT OUTER JOIN mahara.group_member AS gm</font></div>
<div><font face="'courier new', monospace"> ON <a href="http://u.id" target="_blank">u.id</a> = gm.member</font></div><div><font face="'courier new', monospace">WHERE gm.member IS NULL</font></div>
</div><div><font face="'courier new', monospace">OR</font></div><div><font face="'courier new', monospace"> (</font></div><div><font face="'courier new', monospace"> gm.member IS NOT NULL</font></div>
<div><font face="'courier new', monospace"> AND gm.group != 10</font></div><div><font 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 style="font-family:'courier new', monospace">group_member </span>(so no member)</div>
<div>Unless that entry has a <font face="'courier new', monospace">group</font> that isn't '10'</div><div><br></div><div>So you could also use:</div><div><div class="im"><div><font face="'courier new', monospace">SELECT u.*</font></div>
<div><font face="'courier new', monospace">FROM usr AS u</font></div><div><font face="'courier new', monospace"> LEFT OUTER JOIN mahara.group_member AS gm</font></div>
<div><font face="'courier new', monospace"> ON <a href="http://u.id" target="_blank">u.id</a> = gm.member</font></div></div><div><font face="'courier new', monospace">WHERE gm.group IS NULL</font></div>
<div><font face="'courier new', monospace">OR gm.group != 10</font></div><div><font 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"><div><div></div><div class="h5">On 29 June 2011 10:07, Dan Attwood <span dir="ltr"><<a href="mailto:danattwood@gmail.com" target="_blank">danattwood@gmail.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5"><div class="gmail_quote"><div><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></div></div><div class="im">_______________________________________________<br>
Kent mailing list<br>
<a href="mailto:Kent@mailman.lug.org.uk" target="_blank">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></div></blockquote></div><br></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>That will not work. If there are multiple records in gm with member = id and one of them is 10.</div>
<div><br></div><div>Works fine if you have a 1 to 1 relationship however!</div><div><br></div><div>Sorry for pulling your query apart so quickly.</div><div><br></div><div>Try.</div><div><br></div><div><meta http-equiv="content-type" content="text/html; charset=utf-8"><div>
<div class="im"><div><font face="'courier new', monospace">SELECT u.*</font></div><div><font face="'courier new', monospace">FROM usr AS u</font></div><div><font face="'courier new', monospace"> LEFT OUTER JOIN mahara.group_member AS gm</font></div>
<div><font face="'courier new', monospace"> ON (<a href="http://u.id/" target="_blank">u.id</a> = gm.member and gm.group = 10)</font></div></div><div><font face="'courier new', monospace">WHERE gm.group IS NULL</font></div>
<div><font face="'courier new', monospace">;</font></div></div></div><div><font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace">I've not checked it however.....</font></div>
<div><font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace">Peter.</font></div>