Improvements for JOIN clause

EsioN

Hi.

When typing joins in tables with multipe columns in primary-> foreign key, SP shows me the join hint combining the first column with the others. It will be nice if SP shows me join hit as the complete list of columns.
Ex. select * from table_a a inner join table_b b on a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3.
But SP shows me a list with a.c1 = b.c1, other item: a.c1 = b.1 and a.c2 = b.c1, other item a.c1 = b.c1 and a.c3 = b.c3, etc.

I understand I can simple navigate with arrow key and find the correct keys combination, but for that tables with too much join columns, I aways need to resize SP hit window to find the correct combination

Thanks

Ésio

Bart Read

Hi Esio,


Thanks for asking about this. I'm very glad to be able to say that you can change this behaviour in 3.1, although the option is not exposed through the user interface at this point.

What you need to do is open the EngineOptions.xml file in your favourite text editor (as long as it's not TextPad, make sure you use something else). This file lives in:

C:\Documents and Settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3

Once you've opened this file you'll find a setting, probably about a quarter of the way down, called "IncludePartialFKJoinConditions". Change the value of this setting from "True" to "False" (omit the quotes) and it'll do what you want: the only foreign key based join condition you'll get in the scenario you describe is

a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3

You'll need to make sure you've also switched off name and type matched join conditions though, which you can do in the Options dialog, or you will get additional conditions in your list. At some point in the future we'll likely apply better ordering to the different types of join condition as well, but in 3.1 it hasn't been changed from that in 3.0.

Hope that helps.


Thanks,
Bart