SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Returning two lists from one query

    I have page that lists contact people (liaisons) for each state in the US.

    In my database I have three tables that are associated with this:

    'states' (state_id, state_name)
    'staff' (staff_id, staff_name, etc)
    'liaisons' (id, state_id, staff_id)


    The liaisons table joins the states table to the staff table, and it's a simple query to fetch the states that each staff member is assigned to.

    What's not so simple (for me at least) is to also select the remaining states - the ones that the member is not assigned to. I can do it in a second query, but I'd rather do it in the same query, if possible.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you states table has all stats? If so
    you just need to change your join condition from inner join for left join
    something like

    select s.state_id,f.staff_id,l,staff_id as lstaff_id
    from states s
    left join staff f
    on s.state_id=f.state_id
    left join liaisons l
    on s.state_id=l.state_id

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Well, I actually don't need to join the staff table, because the page where I assign states to staff is on the edit page for that staff member, so I can do:

    WHERE staff_id = 56

    So this query gets me a list of the states assigned to the staff member with the ID 56:

    Code SQL:
    SELECT    states.state_id
    	, states.state_name
    FROM ( states
    INNER JOIN liaisons
    	ON liaisons.state_id = states.state_id
    		)
    WHERE liaisons.staff_id = 56
    ORDER BY states.state_name

    Now I just need to get the remaining states that he's not assigned to, in order to populate the left <select>. I think this is going to be more difficult than I originally expected, because there are dozens of other members assigned to states for other offices (there are 3 offices with state assignments) so I can't just select the states where the staff_id is not 56.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about

    SELECT states.state_id
    , states.state_name
    FROM ( states
    left JOIN liaisons
    ON liaisons.state_id = states.state_id
    )
    WHERE liaisons.staff_id = 56 or liaisons.staff_id is null
    ORDER BY states.state_name

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I think I'm going to go ahead and change the table structure. I've run into some more problems that this one will cause.

    'where staff_id is null' will still return the states with other staff members assigned to them, so I'll still get all the states.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT states.state_id
         , states.state_name
         , liaisons.staff_id  -- this will be null if 56 is not assigned to the state
      FROM states
    LEFT OUTER
      JOIN liaisons
        ON liaisons.state_id = states.state_id
       AND liaisons.staff_id = 56
    ORDER 
        BY states.state_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy,

    The problem is that each state has three staff assignments - one for each office. I made a many-to-many relationship with the table above, but that introduced a bunch of problems.

    The rendered page displays in an HTML table, like this:

    Code HTML4Strict:
    <table>
    <tr><th rowspan="3">State</th></tr>
    <tr><td>Office1</td><td>Name</td><td>Phone</td></tr>
    <tr><td>Office2</td><td>Name</td><td>Phone</td></tr>
    <tr><td>Office3</td><td>Name</td><td>Phone</td></tr>
    </table>

    One of the problems with the many-to-many was that if you deleted a staff member, their row wouldn't appear, and you'd get this:

    Code HTML4Strict:
    <table>
    <tr><th rowspan="3">State</th></tr>
    <tr><td>Office1</td><td>Name</td><td>Phone</td></tr>
    <tr><td>Office3</td><td>Name</td><td>Phone</td></tr>
    </table>

    When what I needed was this:

    Code HTML4Strict:
    <table>
    <tr><th rowspan="3">State</th></tr>
    <tr><td>Office1</td><td>Name</td><td>Phone</td></tr>
    <tr><td>Office2</td><td colspan="2">unassigned</td></tr>
    <tr><td>Office3</td><td>Name</td><td>Phone</td></tr>
    </table>

    Otherwise someone might think that state had no contact (which is entirely possible).

    So now I've changed the table structure by combining the states and liaisons table.

    id | office_code | state | contact_id

    So this gives me something like this:

    1 | Office1| Alabama | 56
    2 | Office1| Alabama | 121
    3 | Office1| Alabama | 29
    4 | Office2| Alaska | 64
    4 | Office2| Alaska | 142
    etc

    Now I can select from this table and left join the staff and not lose my null rows.

    Does this setup horrify you? I hope not, because it seems to fix all my problems.
    <cfset myblog = "http://cydewaze.org/">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Now I can select from this table and left join the staff and not lose my null rows.
    why would you lose your null rows?

    did you try my query (post #6)? or does it no longer work in your revised schema?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    did you try my query (post #6)? or does it no longer work in your revised schema?
    I'd already changed the db around by the time I saw that post.

    Quote Originally Posted by r937 View Post
    why would you lose your null rows?
    Ok, I'll try to explain in my usual, most confusing way, as only I can.


    My office is comprised of five sub-offices. Three of those have state contacts, known as liaisons. I need to build a page that is basically a list of these liaisons, organized by state. I need to populate it based on the staff table in my database. Sometimes a staff member who is a liaison leaves the office (retires, changes jobs, etc), and their replacement isn't chosen immediately. For these instances, I need to still show the state and office, but show "unassigned" in place of the contact name. There are also instances where the liaison's office is in that state, and that needs to be shown with a * next to their name.

    After changing the table, I came up with this:

    Code SQL:
    SELECT    liaisons.office_code
            , liaisons.state_name
            , liaisons.in_office
            , staff.first_name & ' ' & staff.last_name AS fullname
            , staff.phone
    FROM ( (  liaisons
    LEFT JOIN states
           ON states.state_name = liaisons.state_name
            )
    LEFT JOIN staff
           ON staff.staff_id = liaisons.contact_id
            )
    INNER JOIN officeteams
            ON LEFT(officeteams.team_name,4) = liaisons.office_code
    ORDER BY liaisons.state_name, team_order

    Which creates this sort of quirky page.

    Now I just have to finish redoing the back end for it.
    <cfset myblog = "http://cydewaze.org/">


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
  •