Suggesting JOIN conditions

jcc105jcc105 Posts: 7
edited October 23, 2009 10:27AM in SQL Prompt Previous Versions
I would like to offer a suggestion on JOIN conditions.

In many of our databases we have Foreign Key relationships where the key columns do not have the same names, but where the + [Primary Key column name] = [Foreign Key Column]

e.g.
select *
from site
inner join location
on site.locationid = location.id

If I'm in the source database (and assuming the FKs are actually created) then SQL prompt suggests the FK relationship first in the JOIN suggestion list, followed by the matching column names. This is great.

However, we often write code against a replicated copy of the tables, and usually the Foreign Keys are not created in the copy database. Occasionally, also, developers will not create FK relationships, but code their apps to write data as if it were a foreign key. (NHibernate is going to be a thorn in my side on this one!)

It would be helpful, to me particularly, if the JOIN list included "potential" FK relationships where the TableName+ColumnName = PK. An option to order the 3 types of join conditions (FK relationships, Column name matches and potentials) might be useful too.

Does anyone else think this is a useful feature?

Chris

Comments

  • Many thanks for your post.

    I have logged this as a feature request (SP-2848) in our internal tracking system.

    So that this can be viewed by our development team and decide whether it can be a candidate for future release.

    I will keep you updated on this.

    Mean while kindly let us know if you have any issues or questions regarding the product,I'll like to help.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • Hi

    Some of tables use a PK___ID and FK____ID as joins , these normally show in teh suggestion list but not at the top.

    It would be nice to see them promted to teh top based on the ____ID matches ignoring the PK & FK prefixes .

    If that makes sense

    Mike
  • Thanks for the post Mike.

    I have updated the feature request.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
Sign In or Register to comment.