How can we help you today? How can we help you today?

Auto Completing joins

I thought I saw some functionality in version 2 that looked like it wanted to give a candidate list of potention columns to join on when the user started a join syntax. For example typing in:

SELECT *
FROM tblRequest R
INNER JOIN tblRequestType RT ON (R.nRequestType = RT.nRequestType)
INNER JOIN tblTime T ON

Would open a candidate list with ALL of the possible joins (based on foriegn keys) that were available. Typing in "(R" would then limit the list to only possible joins between tblTime and tblRequest eliminating tblRequestType. Is this still going to happen?
johnw
0

Comments

6 comments

  • Bart Read
    You should find that it works out of the box, however you might want to check that the options are set correctly. Go to SQL Prompt > Options via the main menu bar in SQL Server Management Studio or Query Analyzer. Then go to the Candidates tab. Then look under Join Conditions and ensure that the options you want are checked. Foreign key based conditions should always be generated.

    If you have any cases where this appears not to work we'd be very interested to hear from you.


    Many thanks,
    Bart
    Bart Read
    0
  • johnw
    When typing:

    SELECT *
    FROM tblRequest R
    INNER JOIN tblTime T ON (R.nRequestID = T.

    I would think that I would only get items in the candidate list that matched either by foriegn keys or at the least by datatype. I know its possible to join on two different datatypes but it is not best practices.
    johnw
    0
  • Bart Read
    Hi John,


    You need to make sure that the "Include matching column names" option is unchecked, and that the "Include matching data types" option is checked.


    HTH,
    Bart
    Bart Read
    0
  • johnw
    Here is the problem... Everything works the way I would expect it to "IF" I do not use an opening parentheses. So in the example

    SELECT *
    FROM tblRequest R
    INNER JOIN tblTime T ON

    The first item in the candidate list is R.nRequestID = T.nRequestID

    However if I use an opening parentheses AND have the option to automatically close parentheses (SQL Prompt > Auto Insert > Automatic Closing Characters), all of this functionality is out the door. I did notice that by default Parentheses was not checked in this section. Is this a known issue?
    johnw
    0
  • johnw
    Another one.

    I now have the auto complete parentheses turned off so I type

    SELECT *
    FROM tblRequest R
    INNER JOIN tblTime T ON (

    and the correct join options appear, however when I select my join from the candidate list and add the closing parentheses manually the candidate list reappears with a list of columns from tblTime not allowing me to hit "Enter" to go to the next line without first hiting the "Esc" key to close the candidate list.
    johnw
    0
  • Bart Read
    OK thanks John, that's perfect. We'll take a look into it to see what the problem is.
    Cheers,
    Bart
    Bart Read
    0

Add comment

Please sign in to leave a comment.