Re: Help with a query

"John Gilson" <jag_at_acm.org> wrote in message news:i3Mea.836$LJ2.880369_at_twister.nyc.rr.com...
> "john" <nospam_at_nospam.com> wrote in message
news:PZLea.1200$yF3.90097_at_stones.force9.net...
> > Hi,
> >
> > I have the following tables in my database:
> >
> > CREATE TABLE WMS_Allocations (
> > Allocation_ID int(11) DEFAULT '' NOT NULL auto_increment,
> > Project_ID int(11) DEFAULT '0' NOT NULL ,
> > User_ID int(11) DEFAULT '0' NOT NULL ,
> > PRIMARY KEY (Allocation_ID),
> > );
> >
> > CREATE TABLE WMS_Projects (
> > Project_ID int(11) DEFAULT '' NOT NULL auto_increment,
> > Project_Name varchar(255) ,
> > PRIMARY KEY (Project_ID)
> > );
> >
> > CREATE TABLE WMS_User (
> > User_ID int(11) DEFAULT '' NOT NULL auto_increment,
> > User_Username varchar(100) DEFAULT '' NOT NULL ,
> > User_Password varchar(100) DEFAULT '' NOT NULL ,
> > User_Name varchar(100) DEFAULT '' NOT NULL ,
> > User_Type int(11) DEFAULT '0' NOT NULL ,
> > PRIMARY KEY (User_ID),
> > UNIQUE User_Username (User_Username)
> > );
> >
> > How can i select users from WMS_User where no users have been allocated
to a
> > particular project - seems a little tricky with MySQL?
>
> SELECT U.User_ID
> FROM WMS_User AS U
> LEFT OUTER JOIN
> WMS_Allocations AS A
> ON U.User_ID = A.User_ID
> WHERE A.User_ID IS NULL
>
> Regards,
> jag
>
>

Thanks for your reply,

That is almost what I want but I would like to be able to specify the project id that the user isn't allocated to, is this possible?

In other words SELECT ALL USERS THAT ARENT ALLOCATED TO PROJECT X.

Also could you direct me to a tutorial on JOINS, this seems to be something I need to learn,

Many thanks Received on Fri Mar 21 2003 - 23:46:22 CET