I'm working on an SMS plugin for a CMS. The database of users includes multiple cellphone #'s (general demographics as `profile_values` table, and various person status in `user` table (e.g. active, retired, board member, etc.). The objective is to select DISTINCT values such that only active members whose primary cellphone # is user configured to accept SMS msgs (per statuses/ user prefs stored in tables).
In addition to querying for the correct SMS #, I want to include the person's name so the CMS administrator can select that name from a drop-down menu if desired, to compose an SMS message to that user (or perhaps several users at a time... ), vs sending SMS to all active SMS numbers returned in the query.
In my SQL, I can get the SMS #'s I want, but I am having difficulty with the SQL to have the Name returned as well. Please assist and advise (what am i missing/ doing wrong?).
- Code: Select all
SELECT DISTINCT p.value FROM `users` AS u
LEFT JOIN `profile_values` AS p ON u.uid=p.uid
AND p.`fid` = 11 AND p.`value` != ''
LEFT JOIN `profile_values` AS p2 ON u.uid=p2.uid
AND p2.fid = 10
LEFT JOIN `profile_values` AS p3 ON u.uid=p3.uid
AND p3.fid = 13
LEFT JOIN `profile_values` AS p4 ON u.uid=p4.uid
AND p4.fid = 1 AND p4.`value` != ''
WHERE u.`status` = 1
AND (p2.value!='Retiree' OR p3.value = '1')
`profile_values` Table illustration provided
Thank you!