Common Col Pair in WHERE clause

You get a popup for picking column pairs for JOIN ON in FROM clause. I would like to see the same common column list between tables in WHERE clause. Since most of our tables have composite primary key cols and we don't use pure ANSI standard ways of JOINS in FROM CLAUSE we are ending up typing it manually not knowing how to automate using by other means. It would be of great time saving for us if the common col list is also available in the WHERE clause (by default or by using spl. keys).

You may add a user selection option "Show common column list in WHERE clause" Yes/No (let this be 'NO' by default so existing users workflow is not disturbed if they are used to with ANSI JOINS)

May I ask what do you mean by "not pure ANSI standard ways of JOINS"? Matching column pairs (even those being members of the primary key / referencing foreign keys brotherhood) behave radically different depending on what type of join you want to use and where those matches are (ON clause or WHERE clause).

hitesh wrote:

You get a popup for picking column pairs for JOIN ON in FROM clause. I would like to see the same common column list between tables in WHERE clause. Since most of our tables have composite primary key cols and we don't use pure ANSI standard ways of JOINS in FROM CLAUSE we are ending up typing it manually not knowing how to automate using by other means. It would be of great time saving for us if the common col list is also available in the WHERE clause (by default or by using spl. keys).

You may add a user selection option "Show common column list in WHERE clause" Yes/No (let this be 'NO' by default so existing users workflow is not disturbed if they are used to with ANSI JOINS)

If I get it correctly, you are referring to primary key / foreign key columns suggested in the JOINs keyword Intellisense. In recent versions the same kind of suggestion is available also in the FROM clause after typing comma as demonstrated on the sample screenshot. If you choose a table in the suggested, not specific column, it will insert the entire thing as a JOIN, including table name, and all matching columns. If you don't use JOIN syntax, you can simply replace the inserted JOIN keyword with WHERE (4 letters) and keep the rest of the inserted text. While it's not exactly what you had in mind, I believe it makes it very close

JOINs popup is very very different from columns list popup and others, it would be very technically challenging to combine the two. I hope the method I demonstrated can help you too.

That's why I asked what is the non-standard way they are using. The popup that comes for JOINs has two layers. It can assist in joining tables that have no defined connections in the data model (no foreign keys define), in which case it offers the column pairs based on their matching names. These pairs are shown in light gray. But if you have the connection defined (you have created the foreign keys), it also offers to base the join on that attribute. Those conditions are shown in red and they do not add single column pairs, they add the column pairs that are involved in the foreign key, in the case of composite keys it adds all the columns (column pairs). They don't even have to have the same name, the foreign key definition tells everything there is to know. Imho, there's no faster way to add joins. It's on par with (or sometimes even considerably faster than) building the query by selecting/dragging the tables in a diagram.

Of course, this only works if the foreign keys are properly added to the model. Is having foreign keys defined in the database what you referred to as pure ANSI standard way of JOINs, hitesh?

SysOp wrote:

In recent versions the same kind of suggestion is available also in the FROM clause after typing comma as demonstrated on the sample screenshot. If you choose a table in the suggested, not specific column, it will insert the entire thing as a JOIN, including table name, and all matching columns. If you don't use JOIN syntax, you can simply replace the inserted JOIN keyword with WHERE (4 letters) and keep the rest of the inserted text. While it's not exactly what you had in mind, I believe it makes it very close

JOINs popup is very very different from columns list popup and others, it would be very technically challenging to combine the two. I hope the method I demonstrated can help you too.

Wait, what? I don't remember this feature being added. But I'm trying it and when I select the table to be joined, I just get FROM firstTable,SecondTable... I don't get the fully inserted join UNLESS i expand the table data with the plus and select the actual suggested (red) join criteria (which is not auto expanded like it is when I would have typed JOIN.)

The method you suggest when picking the table and not the red join criteria DOES work if I'm actually using the completion after the word JOIN is typed, however.

@Mindflux You are correct, you need to expand that level to make it insert the entire thing, else it will insert just the name.