CODEWITHSUNDEEP
postgresql
Patrick
Patrick

Reputation: 165

Postgresql Null value populates left table when doing a left join. Why is this happening?

When I make the query:

"select * from asset a where (a.login_id = $1) and (a.status = 'pub')";

I get back the expected result:

{
  id: 23f8jfj8gh2,
  name: 'Asset1',
  status: 'pub',
  create_date: 2020-11-12T07:00:00.000Z,
  ...
}

But when I add a left join onto the query like this:

"select * from asset a left join asset_image i on (a.id = i.asset_id) where (a.login_id = $1) and (a.status = 'pub')";

I get back:

{
  id: null,  <---- THIS NULL VALUE for the asset id
  name: 'Asset1',
  status: 'pub',
  create_date: 2020-11-12T07:00:00.000Z,
  ...
  asset_id: null,
  orig_name: null,
  mimetype: null,
  created: null
}

I am new to SQL, and I have looked at the docs for left joins, but I can't seem to figure out exactly where this is going wrong. Any help would be much appreciated! Thanks.

Upvotes: 1

Views: 89

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246093

Your problem is probably the * in SELECT *, which is something that you should always avoid in code.

If the table asset_image also has a column named id, your result set will contain two columns named id. You are probably looking at the wrong one.

Using SELECT * gives you no control over the columns you get in the result set, their name and their order. You should use an explicit column list and give columns with the same name in both tables an alias that allows you to disambiguate them.

Upvotes: 1

Ben H
Ben H

Reputation: 30

If the asset_image table contains its own column with the name of id when your query gets no match between (a.id = i.asset_id) it will overwrite the id field with null.

You may need to define each tables id column with a more unique name eg.

  • asset_id to replace id in the asset table
  • image_id to replace id in the asset_image table

Upvotes: 2

Related Questions