Issue with LEFT JOIN on tables with same column names

[rayzorben]

I am using this query:

SELECT * FROM Rooms r LEFT JOIN Shops s ON r.Shop=s.Number WHERE r.Name=?

In the case where the right table (Shops) matches, the record returns 'Name' from the LEFT table (Rooms).

However in the case where the right table (Shops) has no match, all of the fields for the LEFT table (Rooms) that do not have a conflicting column have data, but in the case of 'Name' it is null because the RIGHT table (Shops) has no match.

I am thinking that maybe it is just iterating the column list and setting the property, and because Shops is last, it will always overwrite Name with null?

It might be solved by SELECT r.Name as Name, * FROM..., but better-sqlite doesn't seem to support that even though it is valid syntax.

I really do want all fields from these tables, and they can change on a new release so I don't want to hardcode what I am selecting either.

[neoxpert]

I don't thinks this is an better-sqlite3 thing, but a sqlite3 one - if it even is one. If you don't want all fields of all involved tables, don't use and define exactly what you expect in your return data set. means literally everything and in case there are multiple columns with the same name spread across all the tables I would expect an error anyway about ambiguous column namings.

[rayzorben]

Both queries work fine in SQLiteStudio

This is a valid SQLite query which does not work in better-sqlite3

SELECT r.Name as Name, * FROM Rooms r
LEFT JOIN Shops s ON r.Shop=s.Number
WHERE r.Name='foo'

I DO want all fields. I know normally it is not best practice but in this case I absolutely want all fields and if more are added in the future, I don't want to have to make a change.

[Prinzhorn]

.expand(true)

Causes the prepared statement to return data namespaced by table. Each key in a row object will be a table name, and each corresponding value will be a nested object that contains the associated column data. This is useful when performing a JOIN between two tables that have overlapping column names. If a result column is an expression or subquery, it will be available within the special $ namespace.

via

https://github.com/WiseLibs/better-sqlite3/issues/556#issuecomment-804309704

via

https://github.com/search?q=repo%3AWiseLibs%2Fbetter-sqlite3+left+join&type=issues

Edit: alternatively .raw(true) will also give you all data

[rayzorben]

Thanks!