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.

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

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:

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.

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

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.

SELECT states.state_id
     , states.state_name
     , [COLOR="#FF0000"]liaisons.staff_id  -- this will be null if 56 is not assigned to the state[/COLOR]
  FROM states
  JOIN liaisons
    ON liaisons.state_id = states.state_id
   AND liaisons.staff_id = 56
    BY states.state_name

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:

<tr><th rowspan="3">State</th></tr>

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:

<tr><th rowspan="3">State</th></tr>

When what I needed was this:

<tr><th rowspan="3">State</th></tr>
<tr><td>Office2</td><td colspan="2">unassigned</td></tr>

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

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.

why would you lose your null rows?

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.

Ok, I’ll try to explain in my usual, most confusing way, as only I can. :slight_smile:

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:

SELECT    liaisons.office_code
        , liaisons.state_name
        , liaisons.in_office
        , staff.first_name & ' ' & staff.last_name AS fullname
FROM ( (  liaisons
LEFT JOIN states
       ON states.state_name = liaisons.state_name
       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.