LEFT JOIN - Query help

[ajaxStardust]

Hello.
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!

[ajaxStardust]

the following gets the data i need, but i can't use it in PHP because it's not distinguishing the table aliases. Perhaps a subquery is what I'm looking for instead? (the `profile_values` table turns out to have no PK. The three columns shown above are all there is to it. an inherited project)

Code: Select all

SELECT DISTINCT
    Nmbr.uid,
    Nmbr.fid,
    Nmbr.value,
    Nm.fid,
    Nm.value,
    u.uid,
    u.status,
    Nm.uid
  FROM profile_values Nmbr
    INNER JOIN users u
      ON u.uid = Nmbr.uid
    INNER JOIN profile_values Nm
      ON Nm.uid = u.uid
  WHERE Nm.fid = 1
  AND Nmbr.fid = 11
  AND Nmbr.value <> ''
  AND (Nm.value <> 'Retiree'
  OR Nmbr.value = '1')

Thanks for reading!

[Altrea]

You can use this with PHP. But you need to mask all identifiers with backticks

Code: Select all

SELECT DISTINCT
    `Nmbr`.`uid`,

...