Multiple Join Criteria (A97)

Topic

New Reply

I have a number of queries created from the query grid that seem to have extraneous join conditions when I view them in SQL. As an example, I have the following query – it seems that the join conditions (Organizations.OrgID = Proposals.OrgID) and Proposals.ProposalName = Contracts.ProposalName are both repeated several times.

Is this just ‘one of those things’ or does this affect performance. If the Jet engine is evaluating the same join criteria a number of times, does that slow down the query? Will I damage things beyond repair if I edit the SQL to take out the second-through-fourth repetitions of the same condition? Although I would likely catch a horrible crash (and revert to a back up – another hard-learned lesson) I am worried that I might miss a subtle flaw that I create this way.

Finally, does anybody have any idea how this sort of thing starts and (if is is a performance issue) what to do to prevent it?

SELECT Proposals.ResourceType, Proposals.TargetCOD, Proposals.ProposalName, Contracts.InitialPeriodDate, Organizations.Company, Proposals.RegionName, Proposals.Capacity, Proposals.Output, Proposals.EvalBy, People.PrimeContact, [People]![Salutation] & ” ” & [People]![Fname] & ” ” & [People]![Lname] & IIf(Not IsNull([People]![Hon]),”, ” & [People]![Hon]) AS Contact, People.Title, IIf(IsNull([People]![DirPhone]),[Organizations]![Phone],[People]![DirPhone]) AS PhoneNo
FROM ((Organizations INNER JOIN Proposals ON (Organizations.OrgID = Proposals.OrgID) AND (Organizations.OrgID = Proposals.OrgID) AND (Organizations.OrgID = Proposals.OrgID) AND (Organizations.OrgID = Proposals.OrgID)) INNER JOIN Contracts ON (Proposals.ProposalName = Contracts.ProposalName) AND (Proposals.ProposalName = Contracts.ProposalName)) INNER JOIN People ON Organizations.OrgID = People.OrgID
WHERE (((Proposals.RFPSequence)=2) AND ((Contracts.EPADate) Is Not Null) AND ((People.PrimeContact)=Yes))
ORDER BY Proposals.ResourceType, Proposals.TargetCOD, Proposals.ProposalName, Proposals.RegionName;

Thanks for any help or suggestions

Reply | Quote

Replies

I have no idea why there are the extra join conditions.

Tell me what does the design View look like?

Are there multiple lines between Organizations and Proposals on OrgID?

You should not create havic by deleting those extraneous joins.

Save the query first as another name then try mucking about with the query.

HTH
Pat

Reply | Quote

Check your relationships layout and see if you’ve somehow created multiple joins there. That would result in them turning up in queries as well.

Reply | Quote

Thanks Patt and Charlotte, for your suggestions. I have not had a chance to do much with them today, but will look at the relationships table tomorrow, and also just try hacking away at the SQL to see what the results are like.

Reply | Quote