SQL show value even the WHERE clause is not true

Question

I have a SQL query that output a total of 48 rows, then I add some table to join the I only get one row as a response,

Here is my first query that output a total of 48 rows

      SELECT
        users.uid,
        users_roles.rid,
        users.mail,
        users.name,
        dependant.field_dependent_type_tid,
        player_name.field_member_name_value
      FROM
        users
        JOIN users_roles
        JOIN field_data_field_dependent_type AS dependant
        JOIN field_data_field_member_name AS player_name
      WHERE
        users.uid = users_roles.uid
        AND users_roles.rid = 13
        AND dependant.field_dependent_type_tid != 374
        AND dependant.entity_id = users.uid
        AND player_name.entity_id = users.uid

And then when I add a mobile number field, the output became 1 row

Here is my second query that output a total of 1 row

    SELECT
      users.uid,
      users_roles.rid,
      users.mail,
      users.name,
      dependant.field_dependent_type_tid,
      player_name.field_member_name_value,
      mobile_number.field_mobile_number_value
    FROM
      users
      JOIN users_roles
      JOIN field_data_field_dependent_type AS dependant
      JOIN field_data_field_member_name AS player_name
      JOIN field_data_field_mobile_number AS mobile_number
    WHERE
      users.uid = users_roles.uid
      AND users_roles.rid = 13
      AND dependant.field_dependent_type_tid != 374
      AND dependant.entity_id = users.uid
      AND player_name.entity_id = users.uid
      AND mobile_number.entity_id = users.uid

As you can see in my query, I add a mobile number field to show what is the mobile number of the user that has a uid equals to the entitiy_id of the mobile_number field, but only 1 out of 48 users have their mobile number registered.

What I want to do is to show all those 48 rows and that includes the one row user with have a mobile number,

So the output will be 47 users with an empty mobile number and one user that has a mobile number, how can I do that in my given sample of query?

Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — OldProgrammer, Jul 15 '19 at 00:25

score 3 · Accepted Answer · answered Jul 15 '19 at 00:49

Change the syntax to use left joins, and put the join conditions with the joins, not in the where clause:

 SELECT
      users.uid,
      users_roles.rid,
      users.mail,
      users.name,
      dependant.field_dependent_type_tid,
      player_name.field_member_name_value,
      mobile_number.field_mobile_number_value
    FROM
      users
      left JOIN users_roles on users.uid = users_roles.uid AND users_roles.rid = 13
      left JOIN field_data_field_dependent_type AS dependant
   on dependant.field_dependent_type_tid != 374
     left  JOIN field_data_field_member_name AS player_name  on dependant.entity_id = users.uid
      left JOIN field_data_field_mobile_number AS mobile_number
 on player_name.entity_id = users.uid and mobile_number.entity_id = users.uid

`LEFT OUTER JOIN field_data_field_mobile_number AS mobile_number ON mobile_number.entity_id = users.uid` did the trick, thank you — Dylan, Jul 15 '19 at 00:51

SQL show value even the WHERE clause is not true

1 Answers1