[Klug-general] Mysql subqueries in PHP

[David Halliday]

Not 100% sure on the PHP side. I know that older versions of MySQL (and this
could be what the PHP is thinking of) didn't have good sub query support.
This could also be a case of what database you are connected to/have access
to (I'm presuming that mahara is a different DB)

In some systems this LEFT OUTER JOIN method can be a lot more efficient than
a NOT IN
You still get everything from table 1 that doesn't have an instance in table
2

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

This doesn't take care of the requirement on table 2 having a condition on
it. I am going to play with some IF/CASE statements and get back to you.



On 29 June 2011 09:13, Dan Attwood <danattwood at gmail.com> wrote:
> I know that isn't strictly Linux but here we go;
> I have the following mysql query:
> SELECT * FROM usr WHERE usr.id NOT IN (SELECT DISTINCT member FROM
> mahara.group_member WHERE mahara.group_member.group='10')
> This selects all users in the usr table that don't have entry for group 10
> in the group_member table
> The query works as expected when run in mysql workbench
> However when I run it in a php script it only run the first part, so does
a
> select * from usr
> Has anyone come across this before and have any idea of a fix?
> Dan
>
> _______________________________________________
> 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/0e6391a3/attachment.htm>