Re: Help with a query

From: john <nospam_at_nospam.com>
Date: Fri, 21 Mar 2003 23:32:44 -0000
Message-ID: <shNea.1221$yF3.92254_at_stones.force9.net>

"John Gilson" <jag_at_acm.org> wrote in message news:_7Nea.1356$iE4.936386_at_twister.nyc.rr.com... > "john" <nospam_at_nospam.com> wrote in message news:_BMea.1212$yF3.91162_at_stones.force9.net...
> >
> > "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.

>
> OK, that's a bit different from my initial understanding of your question.
> This will give you, for each project X, all users not allocated to that

project.

>
> SELECT U.User_ID, U.User_Username, P.Project_ID, P.Project_Name
> FROM WMS_User AS U
>             CROSS JOIN
>             WMS_Projects AS P
>             LEFT OUTER JOIN
>             WMS_Allocations AS A
>             ON A.User_ID = U.User_ID AND
>                   A.Project_ID = P.Project_ID
> WHERE A.User_ID IS NULL
>

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

>
> I don't know one online that I can recommend but you might want to
> search Google for something like "SQL tutorial".
>
> Regards,
> jag
>

> > Many thanks

>
>

Thanks again for your reply but it still isnt quite what i am after, I will be supplying the Project_ID to the query so with PHP the query will look something like 'SELECT ALL USERS THAT ARENT ALLOCATED TO PROJECT'.$_GET[project_id]."'"; Received on Sat Mar 22 2003 - 00:32:44 CET