MySQL Query JOIN returning 1 null field

Question

I have two simple tables:

user

+-------------------------------------------------+
|   uid    |     firstname     |     lastname     |
|-------------------------------------------------|
|   4000   |       Zak         |        Me        |
+-------------------------------------------------+

user_role

+----------------------------------------------+
|   rid    |       uid          |     oid      |
|----------------------------------------------|
|   5      |      4000          |     7000     |
+----------------------------------------------+

I am using this query

SELECT us.firstname, us.lastname, ur.oid
FROM user us
    LEFT JOIN user_role ur
ON us.uid = ur.uid
WHERE us.firstname = 'Zak';

My result is

+-------------------------------------------------+
|     firstname     |     lastname     |   oid    |
|-------------------------------------------------|
|       Zak         |        Me        |  (null)  |
+-------------------------------------------------+

What am I missing? It has to be something simple!

UPDATE

Has to do something with the WHERE clause .. Because if left out, it returns all rows with oid included

Found it. Look at your column names. you have uid and iud — Nomeaning25
– Nomeaning25, Commented Jan 18, 2016 at 20:02
what are the column types for uid on both tables? are they the same? — Nir Levy
– Nir Levy, Commented Jan 18, 2016 at 20:13

HLGEM · Accepted Answer · 2016-01-18 20:29:36Z

4

Follow a process like this:

run this query

SELECT *
FROM user us
WHERE us.firstname = 'Zak';

If you get a result the Where clause is fine. So now you run:

SELECT *
FROM user us
    LEFT JOIN user_role ur
ON us.uid = ur.uid
WHERE us.firstname = 'Zak';

If yuo get no records then there is something wrong with the join. Could be that they have some unprintable characters and 4000 <>4000 as a result. So let's check that.

select * FROM user us where us.uid = 4000
select * FROM user_role us where us.uid = 4000

If one of then does not return a result then there is a data problem where one of the fields contains unprintable characters.

If the select * works, then try the original query again only add a few other fields from the user_role table such as the uid. Then you can see if the join is working but the field is empty or if the join is wrong or possibly you are looking at the wrong field.

SELECT us.firstname, us.lastname, ur.oid, ur.uid
FROM user us
    LEFT JOIN user_role ur
ON us.uid = ur.uid
WHERE us.firstname = 'Zak';

It is also possible the join fields are different datatypes and some type of implicit conversion is messing them up. In that case you probably want to explicitly convert or preferably design your table so that they both use the same data type as they appear to be in a PK/FK relationship.

edited Jan 18, 2016 at 20:29

answered Jan 18, 2016 at 20:13

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HLGEM Over a year ago

Hey this list of possible problems relates to many years of scratching my head trying to figure out why something that looks right didn't work. If I hadn't made such mistakes, I wouldn't now know what to look for,

Collectives™ on Stack Overflow

MySQL Query JOIN returning 1 null field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related