> 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? >
You need to get a little sneaky. Try this: select m1.mgrname as Primary, m2.mgrname as Alternate, pjname FROM managers m1, managers m2, projects, pocs WHERE pjid = 'x' AND pocs.pjid = projects.pjid AND pocs.pmyid = 'x' AND pocs.altid = 'x' AND pocs.pmyid = m1.pmyid AND pocs.altid = m2.altid You join with two separate copies of the managers table, and use one copy to get the primary name and the other copy to get the secondary name. --------------------------------------------------------------------- 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
