Getting information from one table based on info from 3 other tables
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.
- It should then pare down the service providers (portal.portalID -> surg_portal.portalID = surg_portal.surgID), and
- using the (surg_portal.surgID -> office.surgID = office.state) reduce the selected offices to the selected service,
- then select the correct states in which this service is provided (office.state -> states.state = statename, state)
Any questions, suggestions?