SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Common Col Pair in WHERE clause

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Common Col Pair in WHERE clause
Author Message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Common Col Pair in WHERE clause Reply with quote
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)
Wed Sep 15, 2021 2:28 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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).
Sun Sep 19, 2021 2:12 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: Common Col Pair in WHERE clause Reply with quote
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.
Mon Sep 20, 2021 9:54 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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?
Tue Sep 21, 2021 4:06 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 805
Country: United States

Post Re: Common Col Pair in WHERE clause Reply with quote
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.
Tue Sep 21, 2021 8:58 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
@Mindflux You are correct, you need to expand that level to make it insert the entire thing, else it will insert just the name.
Tue Sep 21, 2021 10:54 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.