SQL Query returns unexpected NULL value

Question

When running a SQL query I am getting a strange NULL result. Here is the query:

SELECT * FROM plan LEFT JOIN bill ON plan.planID = bill.planID AND bill.typeID =3 WHERE bill.billID IS NULL

I am running the query in PHP using a PDO object. All of the results are coming out as such:

Array ( [planID] => [0] => 7 [clientID] => 4 ...)

The problem lies within the first value returned. As you can see PlanID is NULL but when the same value is pulled with the numeric key, it appears properly as "7". I have tried to address the issue by adding PDO::FETCH_ASSOC to the query code, but I get the following result:

Array ( [planID] => [clientID] => 4 ...)

I do not understand how or why this is occurring. Any help is much appreciated!

And you're 100% certain you have no null values in your plan table? — Mr. Llama
– Mr. Llama, Commented Jun 5, 2014 at 20:24
Yes, I have pulled up several lines on the back end that match the lines pulled by my PHP and they have the proper PlanID shown in the [0]=> 7 — John Carroll
– John Carroll, Commented Jun 5, 2014 at 20:26

Gordon Linoff · Accepted Answer · 2014-06-05 20:24:19Z

3

I'm not 100% sure this is your problem, but when you say:

select *
from . . .

it is returning all columns from all tables. This means that there are two columns called planId in the results, one from plan and one from bill. The one from bill is NULL, assuming that the purpose of the where clause is to find non-matches. That may be the one that you are seeing.

The solution is to explicitly choose the specific columns that you want. Perhaps:

select plan.planId
from . . .

answered Jun 5, 2014 at 20:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Mr. Llama Over a year ago

I'm not sure if it's legal in mysql, but in oracle you can do table.* to pull from a specific table.

John Carroll Over a year ago

Good Point, doing some testing now.

Collectives™ on Stack Overflow

SQL Query returns unexpected NULL value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related