I'm not sure how to write this query, and could use some direction.

I have 4 tables, I need information selected from only one based on info from the other 3.

The tables | linking fields are:

states | state
office | state, surgID
surg_portal | surgID, portalID
portals | portalID

This query is supposed to return the results of a search.

I created this query, but it did not return all of the results it is supposed to:

SELECT DISTINCT s.state, s.statename FROM states AS s
INNER JOIN office AS o ON o.state = s.state
INNER JOIN surg_portal as sp ON o.surgID = sp.surgID
WHERE (sp.portalID = 3 OR sp.portalID = 4) AND o.listed = 1
ORDER BY s.statename

And I'm not that familiar with JOINs.

What is supposed to happen.

A return of all states where offices are located that provide the desired service. That services is identified by the portal id.

So, portals 3 and 4 are used for this particular site/search.

  1. It should then pare down the service providers (portal.portalID -> surg_portal.portalID = surg_portal.surgID), and
  2. using the (surg_portal.surgID -> office.surgID = office.state) reduce the selected offices to the selected service,
  3. then select the correct states in which this service is provided (office.state -> states.state = statename, state)

Any questions, suggestions?