[Klug-general] Mysql subqueries in PHP

[Peter Childs]

On 29 June 2011 10:54, David Halliday <david.halliday at gmail.com> wrote:

> 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
>>
>
>
> _______________________________________________
> Kent mailing list
> Kent at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/kent
>

That will not work. If there are multiple records in gm with member = id and
one of them is 10.

Works fine if you have a 1 to 1 relationship however!

Sorry for pulling your query apart so quickly.

Try.

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

I've not checked it however.....

Peter.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.lug.org.uk/pipermail/kent/attachments/20110629/9530c1f7/attachment-0001.htm>