Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Help with a query

Re: Help with a query

From: John Gilson <jag_at_acm.org>
Date: Fri, 21 Mar 2003 23:40:55 GMT
Message-ID: <bqNea.1358$iE4.945230@twister.nyc.rr.com>


"john" <nospam_at_nospam.com> wrote in message news: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]."'";

Well, you basically have the answer because the query I provided gives the solution for all projects. If you merely want it for one project, let's assume it's in a variable @project_id, then simplify to

SELECT U.User_ID, U.User_Username, @project_id FROM WMS_User AS U

            LEFT OUTER JOIN
            WMS_Allocations AS A
            ON A.User_ID = U.User_ID AND
                  A.Project_ID = @project_id
WHERE A.User_ID IS NULL

Regards,
jag Received on Fri Mar 21 2003 - 17:40:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US