No subject

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
>

--bcaec548a545b6a31b04a6d75f77
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable