Multiple junction boxes query (1 Viewer)

daniel.winson

Registered User.
Local time
Today, 14:09
Joined
May 28, 2009
Messages
18
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

 

daniel.winson

Registered User.
Local time
Today, 14:09
Joined
May 28, 2009
Messages
18
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?
 
Last edited:

Users who are viewing this thread

Top Bottom