No subject
Tue Feb 22 18:54:42 UTC 2011
he original query allowed for the results to be found in the event of multi=
ple entries and not all had group 10:<div><span class=3D"Apple-style-span" =
style=3D"font-size: 13px; background-color: rgb(255, 255, 255); "><font cla=
ss=3D"Apple-style-span" face=3D"'courier new', monospace">SELECT DI=
STINCT member=A0</font></span></div>
<div><span class=3D"Apple-style-span" style=3D"font-size: 13px; background-=
color: rgb(255, 255, 255); "><font class=3D"Apple-style-span" face=3D"'=
courier new', monospace">FROM mahara.group_member=A0</font></span></div=
><div>
<span class=3D"Apple-style-span" style=3D"font-size: 13px; background-color=
: rgb(255, 255, 255); "><font class=3D"Apple-style-span" face=3D"'couri=
er new', monospace">WHERE mahara.group_member.group=3D'10'</fon=
t></span></div>
<div><font class=3D"Apple-style-span" face=3D"arial, sans-serif"><br></font=
></div><div><font class=3D"Apple-style-span" face=3D"arial, sans-serif">Als=
o we want a list of all the entries from usr that are NOT IN the gm table w=
here the group is 10.<br>
</font><br><div class=3D"gmail_quote">On 29 June 2011 11:28, Peter Childs <=
span dir=3D"ltr"><<a href=3D"mailto:pchilds at bcs.org">pchilds at bcs.org</a>=
></span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0=
0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div><div></div><div class=3D"h5"><br><br><div class=3D"gmail_quote">On 29 =
June 2011 10:54, David Halliday <span dir=3D"ltr"><<a href=3D"mailto:dav=
id.halliday at gmail.com" target=3D"_blank">david.halliday at gmail.com</a>></=
span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
Since I said I'd post a follow up (and I have worked it out after a cof=
fee) using a LEFT OUTER JOIN I shall.<div><br></div><div><div><div><font fa=
ce=3D"'courier new', monospace">SELECT u.*</font></div>
<div><font face=3D"'courier new', monospace">FROM =A0 usr =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 AS u</font></div><d=
iv><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0LEFT OUTE=
R JOIN mahara.group_member AS gm</font></div>
<div><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0ON =A0 =
=A0 <a href=3D"http://u.id" target=3D"_blank">u.id</a> =A0 =A0 =3D gm.membe=
r</font></div><div><font face=3D"'courier new', monospace">WHERE =
=A0gm.member IS NULL</font></div>
</div><div><font face=3D"'courier new', monospace">OR</font></div><=
div><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0(</font>=
</div><div><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0 =
=A0 =A0 =A0 gm.member IS NOT NULL</font></div>
<div><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0AND =A0=
=A0gm.group =A0 =A0 =A0 =A0 =A0 !=3D 10</font></div><div><font face=3D"=
9;courier new', monospace">=A0 =A0 =A0 =A0);</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=A0<span =
style=3D"font-family:'courier new', monospace">group_member=A0</spa=
n>(so no member)</div>
<div>Unless that entry has a <font face=3D"'courier new', monospace=
">group</font>=A0that isn't '10'</div><div><br></div><div>So yo=
u could also use:</div><div><div><div><font face=3D"'courier new', =
monospace">SELECT u.*</font></div>
<div><font face=3D"'courier new', monospace">FROM =A0 usr =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 AS u</font></div><d=
iv><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0LEFT OUTE=
R JOIN mahara.group_member AS gm</font></div>
<div><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0ON =A0 =
=A0 <a href=3D"http://u.id" target=3D"_blank">u.id</a> =A0 =A0 =3D gm.membe=
r</font></div></div><div><font face=3D"'courier new', monospace">WH=
ERE =A0gm.group=A0IS NULL</font></div>
<div><font face=3D"'courier new', monospace">OR =A0 =A0=A0gm.group =
=A0 =A0 =A0!=3D 10</font></div><div><font face=3D"'courier new', mo=
nospace">;</font></div></div><div><br></div>
<div>NOT IN statements are best avoided unless you have a small list of pre=
defined items for performance purposes.</div><div><br></div><div class=3D"g=
mail_quote"><div><div></div><div>On 29 June 2011 10:07, Dan Attwood <span d=
ir=3D"ltr"><<a href=3D"mailto:danattwood at gmail.com" target=3D"_blank">da=
nattwood at gmail.com</a>></span> wrote:<br>
</div></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;bo=
rder-left:1px #ccc solid;padding-left:1ex"><div><div></div><div><div class=
=3D"gmail_quote"><div><br><blockquote class=3D"gmail_quote" style=3D"margin=
:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor=3D"#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=
.=A0</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=3D"mailto:Kent at mailman.lug.org.uk" target=3D"_blank">Kent at mailman.l=
ug.org.uk</a><br>
<a href=3D"https://mailman.lug.org.uk/mailman/listinfo/kent" target=3D"_bla=
nk">https://mailman.lug.org.uk/mailman/listinfo/kent</a><br></div></blockqu=
ote></div><br></div>
<br>_______________________________________________<br>
Kent mailing list<br>
<a href=3D"mailto:Kent at mailman.lug.org.uk" target=3D"_blank">Kent at mailman.l=
ug.org.uk</a><br>
<a href=3D"https://mailman.lug.org.uk/mailman/listinfo/kent" target=3D"_bla=
nk">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 =3D 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 class=3D"im"><div><font face=3D"'courier new', monospace"=
>SELECT u.*</font></div><div><font face=3D"'courier new', monospace=
">FROM =A0 usr =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 AS u</font></div><div><font face=3D"'courier new', monospace">=
=A0 =A0 =A0 =A0LEFT OUTER JOIN mahara.group_member AS gm</font></div>
</div><div><font face=3D"'courier new', monospace">=A0 =A0 =A0 =A0O=
N =A0 =A0=A0(<a href=3D"http://u.id/" target=3D"_blank">u.id</a>=A0=A0 =A0 =
=3D gm.member and gm.group =3D 10)</font></div></div><div class=3D"im"><div=
><font face=3D"'courier new', monospace">WHERE =A0gm.group=A0IS NUL=
L</font></div>
</div><div><font face=3D"'courier new', monospace">;</font></div></=
div></div><div><font face=3D"'courier new', monospace"><br></font><=
/div><div><font face=3D"'courier new', monospace">I've not chec=
ked it however.....</font></div>
<div><font face=3D"'courier new', monospace"><br></font></div><font=
color=3D"#888888"><div><font face=3D"'courier new', monospace">Pet=
er.</font></div>
</font><br>_______________________________________________<br>
Kent mailing list<br>
<a href=3D"mailto:Kent at mailman.lug.org.uk">Kent at mailman.lug.org.uk</a><br>
<a href=3D"https://mailman.lug.org.uk/mailman/listinfo/kent" target=3D"_bla=
nk">https://mailman.lug.org.uk/mailman/listinfo/kent</a><br></blockquote></=
div><br></div>
--bcaec548a545b6a31b04a6d75f77--
More information about the Kent
mailing list