Left Join returned null for columns that have values

Question

I have 2 tables:

table1:

id  |   item_id    |   item_name
1   |      1       |     apple

table2:

id  |   item_id    |   item_price

Table 1 has some data and table 2 will not have any data yet but I want to show them in a html table. I am joining the 2 tables hopefully to get a json object:

{id: 1, item_id: 1, item_name: apple, item_price: null}.

But I got this json object instead which is not desired:

{id: null, item_id: null, item_name: apple, item_price: null}

Using knexjs, this is the code that I use to join the tables:

database.select ('*') from ('table1).leftJoin('table2', 'table1.item_id', 'table2.item_id').then(function(data) {
console.log(data)
};

Am I joining incorrectly? I am using node express server and a postgresql database for this. I want the id and item_id not to return null since they have values. Or is there a way to get all values from all columns besides joining tables?

Answer 1

I guess the issue with column name overwriting. Do something like-

database('table1')
.leftJoin('table2', 'table2.item_id', 'table1.item_id')
.columns([
    'table1.id',
    'table1.item_id',
    'table1.item_name',
    'table2.price'
    ])
.then((results) => {

})