SELECT statement for a VIEW in MySQL not returning all the wanted rows

Question

I need help with my MySQL query:

CREATE OR REPLACE 
    DEFINER = CURRENT_USER
    VIEW users_phpscheduleit
AS
    SELECT 
            u.uid AS memberid,
            pass AS password, 
            mail AS email,
            pv1.value AS fname,
            pv2.value AS lname,
            pv3.value AS phone,
            e_add, e_mod, e_del, e_html, e_app, 
            is_admin, lang, timezone_scheduleit, 
            institution, logon_name
        FROM 
            users u, profile_values pv1, profile_values pv2, profile_values pv3
        WHERE
            u.uid = pv1.uid AND u.uid = pv2.uid AND u.uid = pv3.uid
            AND 
            pv1.fid = 26
            AND
            pv2.fid = 27
            AND 
            pv3.fid = 8;

This cannot contain a list of things, in order for it to remain editable using the view table in an INSERT or UPDATE query in a PHP application I am integrating with another PHP system. My users table is 442 long and I'm only getting 1/2 of them.

Problem:
I am not able to get all the rows in the users table because they obviously don't all have a profile_values.fid of 26, 27 or 8.

Q: How can I tweak the statement to still include those but without breaking the rules that enable editability?

ps.for the curious I'm trying to authenticate phpScheduleIt users through the Drupal DB

Answer 1

I am not able to get all the rows in the users table because they obviously don't all have a profile_values.fid of 26, 27 or 8.

That's because your query uses INNER JOINs, when you need to use an OUTER (IE LEFT/RIGHT) JOIN if you want data where there's a possibility of no supporting record. Your query is in ANSI-89 syntax, which doesn't consistently implement OUTER JOIN syntax. Here's your query rewritten using ANSI-92 syntax:

   SELECT u.uid AS memberid,
          pass AS password, 
          mail AS email,
          pv1.value AS fname,
          pv2.value AS lname,
          pv3.value AS phone,
          e_add, e_mod, e_del, e_html, e_app, 
          is_admin, lang, timezone_scheduleit, 
          institution, logon_name
     FROM USERS u
LEFT JOIN PROFILE_VALUES pv1 ON pv1.uid = u.uid
                            AND pv1.fid = 26
LEFT JOIN PROFILE_VALUES pv2 ON pv2.uid = u.uid
                            AND pv2.fid = 27
LEFT JOIN PROFILE_VALUES pv3 ON pv3.uid = u.uid
                            AND pv3.fid = 8