Here's what I did. Based on the same query (the one you provided) as below I executed the following:
select mgrname, pjname from managers, projects, pocs WHERE pjid = 'x' AND pocs.pjid = projects.pjid AND pocs.pmyid = 'x' AND pocs.altid = 'x' AND (pocs.pmyid = managers.pmyid OR pocs.altid = managers.altid) and it worked fine. However, what I now get is both names of managers associated with pjid ='x' in two seperate lines. What I mean is, instead of getting project.name | primary name | alternate name I get project name | primary name , project name | alternate name. What I want is the first example.... project name | primary name | alternate name ..... all on the same row. Is this achieved by an outer join? If so, can somone give me a syntax example or point me to some good reading online? ----- Original Message ----- From: "David Gilmour" <[EMAIL PROTECTED]> To: "Randy Hammons" <[EMAIL PROTECTED]> Sent: Monday, October 28, 2002 8:31 PM Subject: Re: i almost hate queires, please help!!! > Randy, > > See below > ----- Original Message ----- > From: "Randy Hammons" <[EMAIL PROTECTED]> > To: "mysql" <[EMAIL PROTECTED]> > Sent: Tuesday, October 29, 2002 4:17 PM > Subject: i almost hate queires, please help!!! > > > > here's my table syntax: > > > > create table Projects > > ( PjID int unsigned auto_increment not null primary key, > > PjNumber varchar(40) not null, > > PjName tinytext not null, > > PjDesc tinytext, > > PJEcd varchar(30), > > PJCost varchar(30), > > LastUpdate char(30) > > ); > > > > create table Managers > > ( MgrID int unsigned auto_increment primary key, > > MgrName varchar(40) not null, > > PrimaryID int unsigned not null, > > AlternateID int unsigned not null > > ); > > > > create table PjStatus > > ( StatID int unsigned auto_increment not null primary key, > > PjID int unsigned not null, > > Actions longtext, > > Status varchar(10) > > ); > > > > create table POCs > > ( PjID int unsigned not null, > > pmyid int unsigned not null, > > altid varchar(5) not null, > > > > I only have two records stored in my database currently. You'll notice > the > > "managers" table has 3 id's for each manager. MGRid is the PK only reason > > for that. PrimaryID and Alternate ID are both for tracking purposes. A > > manager might be the "primary" manager (meaning the one ultimately > > responsible for the project) or he may be an alternate (backing somone > else > > up who is the primary). Therefore, these two columns will have a one to > > many relationship with the POCs table. Here's what I want to do: > > > > define a query that will select * from the POCs table and display that > info > > via a php script in a table. Simple enough right? WRONG! I know plently > > about php but jack about SQL. My query is as follows: > > > > select mgrname, pjname > > from managers, projects, pocs > > where pjid = 'x' > > and pmyid = 'x' > > and altid = 'x' > > > > Try > select mgrname, pjname > from managers, projects, pocs > where pjid = 'x' > and pmyid = 'x' > and pmyid=managers.Mgrid > > If you want the altid you will need to do an outer join. whcih you will need > to read up about > > > NOW! This should give me an output like this: > > > > +------------------------------------------------------------------+ > > |__pjname____|___mgrname(primary)___|___mgrname(alternate)_| > > | testproject testprimary1 testprimary2 > > > > Instead, what I get is the name of the project with EVERY single manager > > that I have loaded. Now I only have two records (or 2 projects) loaded > into > > this database, but I have over 90 different project managers loaded (don't > > ask why, lost a bet on that one). So what happens is, I get the > > "testproject" name for the pjname and I get it replicated 94 different > times > > for each and every manager that I have loaded. So it seems like every > > single manager is associated with this project. WTF am I doing wrong? > This > > is going on 11 days trying to figure this out. Am I doing the joins wrong > > or something? Does my table design not allow me to do what I'm trying to > > do? Should I instead be trying to nest 3 queries in one and display the > > output side by side (sort of like the html talbe theory???)?????? > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
