MySQL Forums
Forum List  »  Newbie

query help
Posted by: Lori Johnson
Date: July 28, 2005 07:46AM

Hello,
I am in need of help with a query that I am using for a search results page for a company project database. On the search page, I have 9 search options (several of these with a drop down menu). Just to name a few, users can search by Owner, by State, by Market, and by Consultant. Each project will only have one "result" for any of these search options. The problem I am having is when I add the search option for Subcontractors (which has a drop down menu)--each project can have more than one Subcontractor. For example, project number 3024 has 3 subconstractors, and the query now returns 3 projects for the State of NY (because project 3024 is located in NY) when there is really only one project (3024) that is located in NY.

So how can I alter my query to allow the Subcontractor seach option to return results for all 3 subcontractors for 3024 (by searching 3 times by different subcontractors) but have the State search option return only one result for the state of NY instead of returning the same project 3 times.

Heres my query:

SELECT DISTINCT Project.PrjJobNumber, Project.PrjName, Project.PrjMarket, Project.PrjState, Owner.OwnOrg, ConsltComp.ConsltCompOrg, SubContrComp.SubContrCompOrg

FROM Project, Owner, crConslt, ConsltComp, crSubContr, SubContrComp

WHERE Project.PrjOwnerID=Owner.OwnID AND Owner.OwnOrg LIKE '%varOwn%' AND Project.PrjState LIKE '%varState%' AND Project.PrjMarket LIKE '%varMarket%' AND Project.PrjJobNumber=crConslt.crConsltJobNum AND crConslt.crConsltCompID=ConsltComp.ConsltCompID AND ConsltComp.ConsltCompOrg LIKE '%varConslt%' AND Project.PrjJobNumber=crSubContr.crSubContrJobNum AND crSubContr.crSubContrCompID=SubContrComp.SubContrCompOrg AND SubContrComp.SubContrCompOrg LIKE '%varSubcontr%'

Thanks in advance,
Lori

Options: ReplyQuote


Subject
Written By
Posted
query help
July 28, 2005 07:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.