[Klug-general] Mysql subqueries in PHP

[David Halliday]

How does one amuse a many to many relationship other than tell
it multidimensional jokes? Sorry I couldn't resist that one.

An alternative way to work with the many to many could be a group by but
then it does depend on how many records and columns are coming back.

On 29 June 2011 12:07, Peter Childs <pchilds at bcs.org> wrote:

> 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
>>
>
>
> _______________________________________________
> 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/8308b585/attachment.htm>