Need help with inner Join - Get name instead of ID

ChristopheA · September 27, 2020, 5:04pm

Hi everyone,

I’ve checked a lot of threads here but I’m still stuck with something: Getting the name instead of the ID of a second table with a Join.

I have these two tables:

Posts (rows: ID, Title, Category) - The Category row is the Reference for the ID from the Categories table
Categories (ID, Title) - The

In my page, with the repeat component, I try to show all the posts from my DB and just after the Post Title I would like to show the Category Title associated.

It’s currently showing the Category ID and not the Category Title.

It seems that I need to use the Inner Join (or Left or Right) in order to achieve this, but I can’t get it work unfortunately.
I’ve checked a lot of posts, even those:

Every time I try the solutions I get this message:

{status: "500", code: "ER_DUP_FIELDNAME",…}
code: "ER_DUP_FIELDNAME"
message: "select count(*) as `Total` from (select `Posts`.`id`, `Posts`.`category`, `Posts`.`title`, `Posts`.`description`, `Posts`.`tag`, `Posts`.`slug`, `Posts`.`public`, `Posts`.`creation_date`, `cat`.`id`, `cat`.`title`, `cat`.`description`, `cat`.`slug` from `Posts` left join `Categories` as `cat` on `cat`.`title` = `Posts`.`category` where `Posts`.`public` = true order by `Posts`.`id` DESC) as `t1` limit 1 - ER_DUP_FIELDNAME: Duplicate column name 'id'"
stack: "Error: ER_DUP_FIELDNAME: Duplicate column name 'id'↵    at Query.Sequence._packetToError (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)↵    at Query.ErrorPacket (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)↵    at Protocol._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:291:23)↵    at Parser._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:433:10)↵    at Parser.write (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:43:10)↵    at Protocol.write (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:38:16)↵    at Socket.<anonymous> (/opt/node_app/node_modules/mysql/lib/Connection.js:88:28)↵    at Socket.<anonymous> (/opt/node_app/node_modules/mysql/lib/Connection.js:526:10)↵    at Socket.emit (events.js:315:20)↵    at addChunk (_stream_readable.js:295:12)↵    --------------------↵    at Protocol._enqueue (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:144:48)↵    at Connection.query (/opt/node_app/node_modules/mysql/lib/Connection.js:198:25)↵    at /opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:135:18↵    at new Promise (<anonymous>)↵    at Client_MySQL._query (/opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:129:12)↵    at Client_MySQL.query (/opt/node_app/node_modules/knex/lib/client.js:169:17)↵    at Runner.query (/opt/node_app/node_modules/knex/lib/runner.js:151:36)↵    at /opt/node_app/node_modules/knex/lib/runner.js:40:23↵    at /opt/node_app/node_modules/knex/lib/runner.js:277:24↵    at processTicksAndRejections (internal/process/task_queues.js:97:5)"
status: "500"

This error message is pretty clear: Duplicate column name ‘id’
BUT I don’t know how I could fix it, I can’t rename the ID in my Database structure with Wappler.

I just would like to be able to select the category name and show it in my content page for every post (it’s a list of posts).

Does anyone know how I could do this?

Hyperbytes · September 27, 2020, 6:26pm

Justy give one of the ID fields in the query an alias like this

ChristopheA · September 27, 2020, 6:36pm

Awesome! Thanks a lot, it worked!

Emiel · January 25, 2023, 10:00am

I get the same error (ER_DUP_FIELDSNAME), but adding aliases doesn’t solve the issue.

Also strange is that this error doesn’t show when querying the local SQLite database, however it does on the remote target’s MySQL database.

This is the database query:

SELECT Products.*, Translations.* AS Translations, Pricing.* AS Pricing
FROM Products
INNER JOIN Product_Translations AS Translations ON Translations.Product_id = Products.id INNER JOIN Product_Pricing AS Pricing ON Pricing.Product_id = Products.id
WHERE (Translations.Language = :P1 /* {{$_GET.language}} */ AND Pricing.Country = :P2 /* {{$_GET.country}} */ AND Products.CategoryID IN ({{$_GET.categories.split(',')}}) AND Products.BrandID IN ({{$_GET.brands.split(',')}}) AND Products.CollectionID IN ({{$_GET.collections.split(',')}}) AND Products.StyleID IN ({{$_GET.styles.split(',')}}))

And this is the full response:

{"status":"500","code":"ER_DUP_FIELDNAME","message":"select count(*) as `Total` from (select `Products`.*, `Translation`.*, `Pricing`.* from `Products` inner join `Product_Translations` as `Translation` on `Translation`.`Product_id` = `Products`.`id` inner join `Product_Pricing` as `Pricing` on `Pricing`.`Product_id` = `Products`.`id` where (`Translation`.`Language` = 'en' and `Pricing`.`Country` = 'US')) as `t1` limit 1 - ER_DUP_FIELDNAME: Duplicate column name 'id'","stack":"Error: ER_DUP_FIELDNAME: Duplicate column name 'id'\n"}

I do understand the problem is that the id fields have the same name in all sub tables. When the sub tables’ (inner join tables’) data are removed from the output columns, the error is not thrown, but of course a lot of data is missing. Setting aliases for these sub tables doesn’t solve the issue unfortunately.

Any ideas how to solve this?

Hyperbytes · January 25, 2023, 10:12am

Sounds like you have a field in both tables which have the same name.
You should allocate an alias to one of them so the name is unique. This will require selecting the individual fields rather than using table.* and then you can allocate an alias via the query builder

Emiel · January 25, 2023, 10:20am

Thanks for the prompt reply!

Yes indeed the id fields have the same name and can’t be changed in Wappler.
I also thought of selecting all the fields individually instead of the full table to be able to set aliases for the id fields. However, I would rather find a solution that allows the full (sub) tables to be selected as it’s more future proof (in case fields are added to the tables later on). Do you think selecting fields individually is the only way this could be solved?

And what could declare that this error is thrown on (more extensive) MySQL and not on SQLite?! I would expect if SQLite can handle the query properly, MySQL would be able to do so as well.

sitestreet · January 25, 2023, 10:34am

One quick tip… I might have two tables where they will be joined in my query.

Tables: counties, towns

Counties has CountyID (int 11 AI), CountyName (varchar 30)
Towns has TownID (int 11 AI), tCountyID (int 11), TownName (varchar 30)

So my field in the Towns table which joins with the Counties table has a slightly different name. The ‘t’ at the starts is the initial of the table (Towns) which I find makes everything logical when using it later.

I hope that’s helpful.

Emiel · January 25, 2023, 10:41am

I am using sub tables and didn’t need to setup Reference fields. Those are created automatically then I guess, like this one for example:

I could rename all the ‘id’ fields in all the tables though, would that be a solution you think?

sitestreet · January 25, 2023, 10:45am

Aah, I’m not yet using the Database Manager in Wappler much and have preferred to stick with my normal method of working with data so I’m not going to be much help with that. Sorry. I do plan to start using it, I just need to get my head around how they name things and get used to then working with those structures.

Emiel · January 25, 2023, 11:01am

No problem, thanks anyway!

Hyperbytes · January 25, 2023, 11:42am

Actually it does not future proof against new fields being added as the query schema still needs to be updated so you still need to open and reserve the query.
So your choices are use aliases or chamde the table field names (which is also problematic as wappler bd manager does not allow changing of I’d fields retrospectively, you will need to use an external editir)

Emiel · January 25, 2023, 11:48am

Thanks, then I suppose the best option is to query for individual field values instead of full sub tables.
Thanks for thinking along and the explanation!

Hyperbytes · January 25, 2023, 11:54am

I think you are in danger of making a very simple problem complicated. Dont know if you watched any of my video tutorials but the issue your area having is why I always rename the id fields when creating tables. Sub tables are the way to go. Specify the fields and alias

Emiel · January 25, 2023, 11:55am

I am using sub tables, but didn’t change the id field names on db creation unfortunately, so I will have to use aliases there then, right?

Hyperbytes · January 25, 2023, 11:57am

Yes, use aliases and in future make ID fields (in fact all if possible) unique. The advice from Jon ( @sitestreet ) about prefixes is a great method of managing field names