[Klug-general] Mysql subqueries in PHP

[David Halliday]

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.

SELECT u.*
FROM   usr                                 AS u
       LEFT OUTER JOIN mahara.group_member AS gm
       ON     u.id     = gm.member
WHERE  gm.member IS NULL
OR
       (
              gm.member IS NOT NULL
       AND    gm.group           != 10
       );

Working on the same principal.
We want every row from the usr table
That doesn't have an entry in group_member (so no member)
Unless that entry has a group that isn't '10'

So you could also use:
SELECT u.*
FROM   usr                                 AS u
       LEFT OUTER JOIN mahara.group_member AS gm
       ON     u.id     = gm.member
WHERE  gm.group IS NULL
OR     gm.group      != 10
;

NOT IN statements are best avoided unless you have a small list of
predefined items for performance purposes.

On 29 June 2011 10:07, Dan Attwood <danattwood at gmail.com> wrote:

>
> Have you tested the subquery in php?
>>
>
> I have and that's where it wasn't working.
> 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
>
> _______________________________________________
> Kent mailing list
> Kent at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/kent
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.lug.org.uk/pipermail/kent/attachments/20110629/3e6676c7/attachment.htm>