He did not specify so you have to amuse a many to many relationship!<br><br><div>Oh the word distinct in the original sub query is unnecessary and will plausibly slow the query down.</div><div><br></div><div>doing some testing with Explain (or MySql equivalent I tend to use PostgreSQL)</div>
<div><br></div><div>Peter.</div><div><br></div><div><br><div class="gmail_quote">On 29 June 2011 11:37, 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;">From what I can see it does work to the spec given by the original query. The original query allowed for the results to be found in the event of multiple entries and not all had group 10:<div class="im">
<div><span style="font-size:13px;background-color:rgb(255, 255, 255)"><font face="'courier new', monospace">SELECT DISTINCT member </font></span></div>
<div><span style="font-size:13px;background-color:rgb(255, 255, 255)"><font face="'courier new', monospace">FROM mahara.group_member </font></span></div><div>
<span style="font-size:13px;background-color:rgb(255, 255, 255)"><font face="'courier new', monospace">WHERE mahara.group_member.group='10'</font></span></div>
<div><font face="arial, sans-serif"><br></font></div></div><div><font face="arial, sans-serif">Also we want a list of all the entries from usr that are NOT IN the gm table where the group is 10.<br>
</font><div><div></div><div class="h5"><br><div class="gmail_quote">On 29 June 2011 11:28, Peter Childs <span dir="ltr"><<a href="mailto:pchilds@bcs.org" target="_blank">pchilds@bcs.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div></div><div><br><br><div class="gmail_quote">On 29 June 2011 10:54, David Halliday <span dir="ltr"><<a href="mailto:david.halliday@gmail.com" target="_blank">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><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><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>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><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>_______________________________________________<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" 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></blockquote></div><br></div></div><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><div>
<div><div><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><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><div><font face="'courier new', monospace">WHERE gm.group IS NULL</font></div>
</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><font color="#888888"><div><font face="'courier new', monospace">Peter.</font></div>
</font><br>_______________________________________________<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></blockquote></div><br></div></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>