SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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.

    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?
    John

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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...
    Code:
      FROM surg_portal AS sp
     WHERE sp.portalID IN (  3, 4 )
    then from the surg_portal table, you join the others in sequence...
    Code:
      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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    John


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •