user-site affiliations
hi, I am trying to retrieve all the users and their site affiliations for a given academic year? I am stuck with retrieving the site names that the users are affiliated with for a given academic year. Any help will be appreciated
-
If you have users with multiple site affiliation, public.user_term_site_aff might not work.
Here's a SQL query that might be helpful.
SELECT DISTINCT u.user_id, u.first_name, u.last_name, site_id, site_name, academic_year
FROM users AS u
JOIN user_term_role_aff USING (user_id)
JOIN terms USING (term_id)
JOIN sessions USING (session_id)
JOIN sites USING (site_id)ORDER BY user_id
Please sign in to leave a comment.
Comments
6 comments