[Klug-general] Mysql subqueries in PHP

[Peter Childs]

He did not specify so you have to amuse a many to many relationship!

Oh the word distinct in the original sub query is unnecessary and
will plausibly slow the query down.

doing some testing with Explain (or MySql equivalent I tend to use
PostgreSQL)

Peter.


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

> 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:
> SELECT DISTINCT member
> FROM mahara.group_member
> WHERE mahara.group_member.group='10'
>
> Also we want a list of all the entries from usr that are NOT IN the gm
> table where the group is 10.
>
> On 29 June 2011 11:28, Peter Childs <pchilds at bcs.org> wrote:
>
>>
>>
>> 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.
>>
>> _______________________________________________
>> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.lug.org.uk/pipermail/kent/attachments/20110629/b954485a/attachment-0001.htm>