Re: Help with a query

"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

> 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
Received on Sat Mar 22 2003 - 00:21:30 CET