Incremental numbers added to table aliases

When suggesting table joins SqlAssistant will auto-increment the table aliases for every possible relational join it can find.

For example here is a screenshot of writing a query to join tables "Clients" and "Operators". There are 4 foreign key joins between these tables so Sql Assistant offers 4 different ways of joining with aliases "O, O2, O3 and O4" for each one.

In theory this is a good way of ensuring every join has a unique alias but in practise it is very annoying because I invariably want only one of the offered joins so I have to remove the extra, unwanted digits.

Also SqlAssistant doesn't pick just the relational joins in the current JOIN but increments the table alias for all possible links from other tables in the query. I've seen digits as high as 15 in our database!

The digit is always wrong for me and I would like to disable it.

Not sure if this is relevant but I am using the "Custom Aliases" feature (Options: DB Options: target "Sql Server": Aliases: "Custom Aliases") where I have a long list of my preferred alias for each table.

I'm sorry I fail to understand what can be changed there. Are you suggesting to show the same aliases in all JOIN options? What would other people do if they need multiple joins, change aliases manually because they will duplicate and the generated code is incorrect?

Can you please describe how you see that working to support different use cases?

Yes, I want to have the same alias offered for all suggested JOIN options.

For me, and likely for most people, it is very, very rare that more than one of the suggested joins is used to link tables. If you think about it, there is normally only one 'correct' way to link tables.

Sql Assistant is being very helpful by suggesting links from all higher tables in the FROM structure which is great but as a user I will only ever pick one of the suggested joins so there is no need to add these numbers to the aliases.

So if in the example below I was to start a JOIN to a Table "Users" which has lots of potential joins then I will get suggestions with aliases U1, U2, U3, U4, etc.

	Code:
	FROM Clients AS C JOIN Contacts AS Cnt ON Cnt.clientKey = C.clientKey JOIN Quotations AS Q ON Q.clientKey = C.clientKey JOIN Users AS U ON ... ...<suggestions>... ...ON U1.userKey = C.userKey ...ON U2.userKey = Cnt.userKey ...ON U3.userKey = Q.userKey ...ON U4.userKey = Q.salesUserKey ...ON U5.userKey = .....

Even though there are multiple suggestions I will only pick one of these and then I have to manually remove the number from the alias.

In the rare case that multiple links are used then yes, I would expect to manually adjust the aliases to allow for this. This is the absolute edge-case and not frequent.

I am using "Custom Aliases". I want Sql Assistant to use the aliases I pre-configured without adding numbers!

They really are a pain in my case because my company's data-schema has double-key indexes joining all tables meaning I have to remove the numbers twice for every join. Also we have code contributors from different teams and countries so we really need to get consistent table-aliases and code-conventions for interoperability.

These auto-increment numbers in the aliases are relatively new. Before approximately version 10 of SQL Assistant it did not do this. Previously it would provide the Aliases exactly as requested and without number-increment.

This might appear trivial to you but actually it is a major impediment to getting "in the flow" with Sql Assistant and stops me smoothly and quickly generating JOINS with the benefit of its intellisense auto-correction. It truly breaks what would otherwise be a great coding-experience.

Regards, Brendon.

If you consider the screenshot I provided earlier:

=> there are four suggested JOINS due to four different foreign key relationships
=> of these the user will (99% normally) only pick one of them
=> so there is no need add the number-increments to the aliases and they are actually counter-productive

If you think about how relational databases work then you'll recognise that each foreign-key relationship defines an internally self-consistent link between tables and different relationships are mutally exclusive.

In each JOIN the user will pick only one foreign-key relationship. This is enforced by SQL Assistant! If I try to simultaneously select two suggestions then SQL Assistant splits these into two JOIN clauses.

So SQL Assistant should add the number increments only when two separate JOINS are created or when the alias has already been used in an earlier JOIN, for example

	Code:
	FROM Clients AS C JOIN Contacts AS Cnt ON Cnt.clientKey = C.clientKey JOIN Contacts ON.... ....<suggestions> .... Cnt1.contactKey = C.invoicingContactKey .... Cnt1.contactKey = C.logisticsContactKey .... Cnt1.contactKey = C.salesPersonContactKey .... Cnt1.contactKey = ....

So because there is an earlier JOIN to Contacts with alias Cnt then in a new JOIN the suggestions would use Cnt1 as alias.
=>Every suggestion uses "Cnt1" because user only selects one suggestion
=>The number differentiates from the earlier JOIN, not from other suggestions!

This is a very subjective description. I don't have the numbers to prove that "... (99% normally) only pick one of them" is just not the case. In my opinion it's highly unlikely and not true, and I'm not sure why you think so. Maybe in your use case scenario you never use multiple joins, but we cannot assume based on that all other use cases are like yours. What you propose will lead to generation of invalid code and likely to multiple bad side effects. We shouldn't change that. Perhaps we can add a new option/feature for users like you to disable adding numeric suffixes.

Last edited by SysOp on Thu May 02, 2024 7:47 pm; edited 1 time in total

I would be perfectly happy if you provided an option to disable the numerical increments to aliases and left the default behaviour as it is currently is.

You have to understand that the different JOIN options are mutually exclusive. That is why they are defined in different foreign key releationships and that is why SQL Assistant is correctly offering them as different lines.

So it is not necessary to auto-add an incremental number to each alias because the user only picks one.

Considering the screenshot I provided before:

	Code:
	From Clients AS C JOIN Operators AS O ....<suggestions> ....O1.operatorKey = C.lastModifiedKey ....O2.operatorKey = C.createdByKey ....O3.operatorKey = C.salesmanResponsibleKey ....O4.operatorKey = C.asmResponsibleKey

So when joining from "Clients" to "Operators" we pick either "lastModified", "CreatedBy", "SalesMan" or "ASMResponsible" or whatever other relationship is available.
We only pick one relation per JOIN so the numbers 1, 2, 3, 4 are not necessary. I can't simultaneously link Clients by both "CreatedBy" and "SalesMan". Those are different, exclusive relations and it is a fundamental part of how relational databases work that these are considered independent, orthogonal links. Even if the same individual operator was coincidentally both "creator" and "salesman", we would still use two different JOINS to query the relationships.

I challenge you to use Sql Assistant in one of your own databases to JOIN tables with multiple foreign-key relations. You will see that Sql Assistant correctly forces you to make two distinct JOINS. In this case I can accept adding numbers to the 2nd and subsequent JOINs but if I add just one JOIN then this should not have the number-increments! Even if I pick the 2nd or later join from list of suggestions I expect no number-increment unless I simultaneously pick more than one join.

Note that the situation in Sql Assistant is particularly bad because the suggestions include all possible relations to tables earlier in the FROM statement. So for example if I have multiple tables linking to the current JOIN then I get multiple aliases with increasingly high number-increment. For example if I add a join to new tableF which has links to other tables earlier in the FROM sequence then I get something like this:

	Code:
	From TableA AS TA JOIN TableB AS TB ON TB.keyA = TA.keyA JOIN TableC AS TC ON TC.keyB = TB.keyB JOIN TableD AS TD ON TD.keyC = TC.keyC JOIN TableE AS TE ON TE.keyD = TD.keyD JOIN TableF AS TF ON.... ....<suggestions> .... TF1.keyE = TE.keyE .... TF2.keyD = TD.keyD .... TF3.keyC = TC.keyC .... TF4.keyB = TB.keyB .... TF5.keyA = TA.keyA

So for common tables with lots of relational links I often get very high numbers in the alias that I never want and always have to stop, go back and delete.

The foreign keys are defined in our relational database. We are not special in doing this. It is common in all relational databases (MS-SQL, Oracle, etc). Each foreign key relation is an independant link so users pick only one at a time per JOIN. To use two foreign keys links you have to make two JOINS. That is common for all SQL users, not just in my company's schema.

I profoundly disagree with you that they are mutually exclusive. I think you're also completely overlooking cases of joinable tables having names starting with the same letter, or having same letters in a camel case naming, which is very common. It's also perfectly fine to join multiple instances of the same lookup table for returning descriptive status names instead of codes, trust me this is a very common use case too, although I cannot offer a percentage number. Removing numeric suffixes would lead to tons of issues.

As an argument I can also refer to the Sakila database, which is one of the most widely used sample databases in the world and modeled on a typical though simple schema design https://www.jooq.org/sakila. In the examples queries they join store, customer, and two instances of the address table in a single query, one for the store and one for the customer, as well as two instances of city and two instances of country. Note that customer, country, and city tables all start with the same letter, and all are referenced by foreign keys, and they are all will show up in the same join list.

Anyway, I'm submitting an enhancement request to add an option for disabling numeric suffixes.

Great, all I want is for Sql Assistant to use the aliases I provide in "Custom Aliases".

If your enhancement request can create an option to do that I'd be very grateful.

In your example from Sakila you didn't link to the code but judging from their schema diagram I guess it is something like this:

	Code:
	FROM Stores AS St JOIN Address AS ASt ON ASt.address_ID = St.address_ID JOIN City AS CASt ON CASt.city_ID = ASt.city_ID JOIN Country AS CouCASt ON CouCASt.country_ID = CASt.country_ID JOIN Customer AS Cu ON Cu.store_ID = St.store_ID JOIN Address AS ACu ON ACu.address_ID = Cu.address_ID JOIN City AS CACu ON CACu.city_ID = ACu.city_ID JOIN Country AS CouCACu ON CouCACu.country_ID = CACu.country_ID

When I say "relations are exclusive" I mean that when multiple links are present we choose only one at a time.

So if you imagine writing the final join to Country there will be two suggestions from SQL Assistant
>from the store-city -> to country
>from the customer-city -> to country

and I'll be picking only one of these at a time so don't need the number-suffix.

Here is another example using the Sakila schema that might explain our problem:

	Code:
	FROM Store AS St JOIN Staff AS Sf ON Sf.store_ID = St.store_ID JOIN Customer AS Cu ON Cu.store_ID = St.store_ID JOIN Address AS A.... ....<suggestions> .... ON A1.address_ID = St.address_ID .... ON A2.address_ID = Sf.address_ID .... ON A3.address_ID = Cu.address_ID

When I start writing a join to "Address" then SQL Assistant will offer three suggestions with aliases A1, A2 and A3. I will only choose one of these, either the store-, staff- or customer-address. So I don't need the suffixes A1, 2, or 3. I will only pick one so I only want alias A for Address and have to go back and manually remove the suffix, either 1, 2 or 3.

Of course queries can get long and I might conceivably write a query to get the addresses of both the store and all its customers in which case I'll need to use different aliases. I want to manage this myself.

You solve this by providing "A1, A2 or A3". For us that is more problematic since we mostly only ever link to one of these and so are always having to go back and remove the number suffixes.

If you can deliver an option to disable the numerical increments to table aliases that would be great.

Here is a tracking number for this enhancement request, #SA0044540. It hasn't been prioritized yet, I cannot offer an ETA yet

Shouldn't it really only suggest some iteration of an alias if the original alias, or an iteration has already been exhausted, anyway?

so
JOIN Address as A
ON

should only suggest like a.[column]

then if you joined address again:

join Address as A1
on A1.......... =

By the way using the Sakila demo on SQL Server, I don't get the incrementing FK issue being illustrated:

Actually I take it back:

If I wait for the picker to pop up when typing a table name on the join statement it will do some incrementing oddity. If I continue typing or pick the table name without a suggsted join, when I type ON the picker pops up with no incrementing suggestions.

In this case, c2 and c3 might make sense, but s2 and s3 don't make any sense, since there's no alias of "s" yet in my editor window, it should probably just be "s" for both of those suggestions until one of them is chosen with a checkbox and then refactor the remaining aliases with an incrementing value.

I agree that if an alias has already been used somewhere higher in the FROM block in a previous join then an auto-incremented suffix makes sense in new suggestions since the alias definitely can't be re-used.

Example:

Code:

FROM Store AS St
JOIN Address AS A ON A.address_ID = St.address_ID

JOIN Staff AS Sf ON Sf.store_ID = St.store_ID
JOIN Customer AS Cu ON Cu.store_ID = St.store_ID
JOIN Staff AS Mngr ON Mngr.staff_ID = St.manager_staff_ID
JOIN Address ....
....<suggestions>
.... ON A1.address_ID = St.address_ID
.... ON A1.address_ID = Sf.address_ID
.... ON A1.address_ID = Cu.address_ID
.... ON A1.address_ID = Mngr.address_ID

Since the alias A is already present higher up in the join from Store then a later join to Address can use A1 in the suggestions.

Notice I repeat A1 for everything offered in the suggestion block since I will only pick one of these. I don't need and don't want these to be A1, A2, A3, A4, etc.

This might even be the perfect solution that you could make the default behaviour to make everyone happy and therefore not need to hide it behind a new option.

If you were to click the check box next to all the join suggestions in your original post, would you get four distinct join statements with o - o4 aliases?

If so, I think the way it should work is:

ALL suggested join aliases start at the lowest they can, either no suffix, or lowest suffix. If you check ONE of the joins, the remainder of the suggestions +1 the suffix. So, as you pick them (if you pick more than one) they increment as you go instead.

That way maybe only you need the "O4" join and would have to fix the alias name once you come out of the join popup it would be a little smarter about it and alias them appropriately in order of selection. (In this case, if O wasn't used yet it would join as O and not O4.. or if O was in use it would alias as O1 (etc).

There might be some technical limitations to that as I have no idea how the suggestion popups work or if they can be reloaded/refreshed in the manner I'm suggesting.

Yes, the screenshot in my original post comes from clicking the check-box to "pick this for all joined columns".

Here is a screenshot to make it perfectly clear.

The original reason for this thread is that if I pick the 4th option I get alias "O4" even though I have not used any of the three other options (O, O2 or O3) anywhere.

Your proposal would be the perfect solution. If I pick the O4 selection it would add as "O" unless I were to pick multiple selections from the list of "all joined columns" or another "O" is somewhere present in any other JOINS of the FROM block.

Basically this whole thread could be summarised as "only add numerical suffixes if aliases really are used multiple times".

Well, whatever is decided by SoftTree will hopefully move toward improvement.

I was messing around with Sakila again last night and it suggested one of the joins should be called s5, there weren't any other tables aliased to s NOR were there any other "s tables" in the suggestion popup. So whatever is tracking the counter for aliases seems to go sideways in some instances too.