Getting information from one table based on info from 3 other tables

Databases
1

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?

2

my suggestion is to rewrite the FROM clause of your query in a manner which resembles the sequence of retrievals that you want to make

you start with the table that has the WHERE conditions applied to it, as this will be the “driving” table for the query…

  FROM surg_portal AS sp
 WHERE sp.portalID IN (  3, 4 )

then from the surg_portal table, you join the others in sequence…

  FROM surg_portal AS sp
INNER
  JOIN office AS o
    ON o.surgID = sp.surgID
   AND o.listed = 1
INNER
  JOIN state AS s
    ON s.state = o.state 
 WHERE sp.portalID IN ( 3, 4 )

this, to me, makes more sense

however, it should still return the same results that your original query did

so if “it did not return all of the results it is supposed to” then there is something else wrong

3

Thank you, r937.

I appreciate your input. I’ll have to take a look at the information in the database to determine if the returned results are indeed what I’m looking for.