[Gllug] OT: MySQL : arrgh no subqueries

[Jonathan Dye]

Ian Norton wrote:
> Hi everyone,
> 
> i've been trawling google for ages for a solution to my problem,
> 
> i want to search two tables in mysql, one table has a list of objects,
> the other is a list of groups that the objects can belong to.
> 
> with the following select query i can get a list of the objects
> and show the group details of the group that owns it,
> 
> SELECT Content.name, Groups.gid, Groups.groupname
> FROM Content.Groups
> WHERE Content.gid = Groups.gid;

Should that be:

SELECT Content.name, Groups.gid, Groups.groupname
FROM Content, Groups
WHERE Content.gid = Groups.gid;

or does MySQL have strange syntax?

> All works nicely,
> 
> but,
> 
> If an item in Content is owned by a Group that has been deleted from
> Groups then it is totally omitted in the above results,
> 
> What i want is to return the Content.name of all the objects that
> were not listed from Content by the previous query,
> 
> confused yet?

How about

SELECT Content.name, Groups.gid, Groups.groupname
FROM Content LEFT OUTER JOIN Groups ON Content.gid = Groups.gid
WHERE Groups.gid IS NULL;

Or does MySQL not have Joins?

JD

_____________________________________________________________________
This message has been checked for all known viruses by the 
MessageLabs Virus Scanning Service

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug