[Gllug] OT: MySQL : arrgh no subqueries

[Ian Norton]

I think i will try some joins,

either that or give up and do it in software :-)

On Wed, Jul 09, 2003 at 11:19:58AM +0100, Jonathan Dye wrote:
> 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

-- 
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS dpu s--: a-- C++++ UL++ P+++ L+++ E--- W-- N+ o K w---
O M-- V-- PS++ PE-- Y+ PGP+ t+++ 5++ X++ R+++ !tv b DI D----
G++ e+ h++ r++ y+++
------END GEEK CODE BLOCK------
----- Message of the Hour ------
Moooo :-)

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