Hi,

I am trying to design a query that will ask the user for a 'postcode' and return a list of organisations that operate in that postcode.

My database has two junction tables organisationPostcode and organisationLocation as some of the organisations listed operate in specific postcodes, others operate accross entire regions.

I can query either one easily but when I try to combine it it stops working... if someone can point me in the right direction or provide an example of a similar query in use I'd really appreciate it

http://img40.imageshack.us/img40/1179/relationship.png

Here is the SQL if it helps...

SELECT organisations.organisationName
FROM (organisations INNER JOIN ((locations INNER JOIN organisationLocation ON locations.location = organisationLocation.location) INNER JOIN postcodes ON locations.location = postcodes.location) ON organisations.ID = organisationLocation.organisationID) INNER JOIN organisationPostcode ON (postcodes.postcode = organisationPostcode.postcode) AND (organisations.ID = organisationPostcode.organisationID)
WHERE (((postcodes.postcode)=[Enter postcode]));


I suspect it doesn't work because it is looking for organisations that have relationships in both junction tables, if this is right is there a way around that?

Or do I need two seperate queries?