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.
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
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
LEFT OUTER
JOIN liaisons
ON liaisons.state_id = states.state_id
AND liaisons.staff_id = 56
ORDER
BY states.state_name
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:
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.
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
, 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
