June 11th, 2013, 11:22 AM
Howdy folks, been a while!
I'm having a tough time wrapping my mind around getting this query to work. In short, I'm trying to generate a list of email addresses from our user list, based on a few different parameters.
Basically, I want to get users.username (email address) IF the user is REGISTERED for a particular TENDER OR the user is subscribed to a DIVISION (essentially a subcategorization subscription list). When staff members create a tender, they assign any number of divisions to it, so whoever is subscribed to that particular division will receive an email for it.
users (PK = user_id)
tenders (PK = tender_id)
registrations (PK = reg_id, FK1 = user, FK2 = tender)
divisions (PK = division_id)
tender_divisions (FK1 = tender, FK2 = division)
user_divisions (FK1 = user, FK2 = division)
What I've been using:
tender_divisions.tender = 12
registrations.tender = 12
users.user_id = registrations.user
user_divisions.division = tender_divisions.division
tenders.tender_id = 12
GROUP BY users.user_id
By playing around with it, I can successfully return EITHER the registered users, OR the divisions subscriptions, but not both.
Thank you in advance!
June 11th, 2013, 12:42 PM
ON registrations.user = users.user_id
AND registrations.tender = 12
ON user_divisions.user = users.user_id
ON tender_divisions.division = user_divisions.division
AND tender_divisions.tender = 12
June 11th, 2013, 12:55 PM
Outstanding, thank you! It's not letting me add to your rep for whatever reason, but this is perfect!