MYSQL LEFT JOIN not showing all the data

Question

I am running this SQL LEFT JOIN query in PHP but it's showing one important column which is user_id as null which is not null.

MYSQL QUERY

SELECT
      a.*,
      b.*,
      c.*,
      d.*
  FROM ts_users_skills a
  LEFT JOIN ts_students_log b
      ON a.`user_id` = b.`user_id`
  LEFT JOIN ts_students_info c
      ON a.`user_id` = c.`user_id`
  LEFT JOIN `geo_records` d
      ON a.`user_id` = d.`user_id`
  WHERE (a.`skillsTeach` = '$skill_1' OR a.`skillsTeach` = '$skill_2'
      OR a.`skillsTeach` = '$skill_3')
  GROUP BY a.`user_id`;

JSON output

    {
    "status": "success",
    "nearby_teachers": [
        {
            "user_id": null,
            "skillsTeach": "PHP",
            "email": "praveenkumarkp666@gmail.com",
            "country_code": "91",
            "username": "praveenkum",
            "name": "Procusa Founder",
            "gender": "M",
            "birthday": "1997-02-25",
            "location": "Bhubaneswar"
        },
        {
            "user_id": null,
            "skillsTeach": "marketing",
            "email": "ashok@procusa.in",
            "country_code": "91",
            "username": "ashok",
            "name": "Ashok the founder 2",
            "birthday": "1993-11-18",
            "location": "Bhubaneswar"
        },
        {
            "user_id": null,
            "skillsTeach": "html",
            "email": "talspo@gmail.com",
            "country_code": "91",
            "username": "talspo",
            "name": "Procusa Founder",
            "gender": "M",
            "birthday": "1997-01-01",
            "location": "Bhubaneswar"

        }
    ]
}

Which table `user_id` comes from? Show the code where you build the result — Jens, Jan 02 '19 at 14:00
Thanks for your advice on Prepared Statement. I am implementing it. — , Jan 02 '19 at 14:02
ts_students_log is the table where user_id is located as a Primary Key but it is present in almost every table as a Foreign key. — , Jan 02 '19 at 14:03
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Feb 13 '20 at 23:25

GMB · Answer 1 · 2019-01-02T14:11:18.717

The problem with your code is that it returns several columns named user_id. Some of those values, that come from LEFT JOINs, may come up as NULL.

When serializing to JSON, where keys are unique, it is likely that the « wrong » field is being chosen, resulting in a NULL value.

To solve this, you want to list precisely the fields to retrieve from each table, instead of using *. Of course, user_id should come from a table that is not LEFT JOINed :

SELECT
  a.user_id, a.field1, a.field2
  b.field11, b.field12
  c.field21, c.field22, c.field23
  d.field31
...

Details

You are selecting fields using the * :

SELECT
  a.*,
  b.*,
  c.*,
  d.*
...

But your query shows that you have multiple fields named user_id :

...
LEFT JOIN ts_students_log b
  ON a.`user_id` = b.`user_id`
LEFT JOIN ts_students_info c
  ON a.`user_id` = c.`user_id`
LEFT JOIN `geo_records` d
  ON a.`user_id` = d.`user_id`
 ...

This is practically the same as my answer but your explanation is way better understandable — Daniel W., Jan 02 '19 at 15:50

score -1 · Accepted Answer · answered Jan 02 '19 at 14:01

-1

You have several columns user_id in different tables. In your JSON there is only one user_id field, it is not clear from which table it is.

It is clear tho, that the data is getting lost at this point, the JSON contains a user_id field from a dataset without a user_id field filled.

Try using user_id from a different table than ts_users_skills a.

answered Jan 02 '19 at 14:01

Daniel W.

31,164
13
93
151

*Try using user_id from a different table than ts_users_skills a* how do you know that OP takes user_id from this table without seeing any code? – Jens Jan 02 '19 at 14:09
Yeah, I got it actually. the geo_records table was the culprit. I removed it and it worked. – Jan 02 '19 at 14:24
Thank you @DanFromGermany for your time. I am indebted and grateful to you. – Jan 02 '19 at 14:25
@Jens no code? He shows is SQL statement. I am programing PHP/MySQL for 18 years now, I have seen all errors, I have seen the OPs problem before and the problem and the reason is just logical - you have several fields, some can be NULL because you LEFT JOIN - result: you picked the wrong field (from the RIGHT side of the dataset). – Daniel W. Jan 02 '19 at 15:48
Thank you again. You people have been of real help. – Jan 08 '19 at 11:32

MYSQL LEFT JOIN not showing all the data

2 Answers2