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

join same table multiple times seems not to work properly

Hello all,

Just downloaded and installed SQL Prompt and am having a bit of a play to see what it can and can't do. First impressions are very good, but trying to join the same table twice in a query seems to confuse the program. Take for example the following code:
select *
from Contact as con
join Address as post on post.AddressId = con.PostalAddressId
join address as street on street.AddressId = con.StreetAddressId
Intellisense works for the first join, but it can't seem to get it's mind around the second one to the same table.

Is this something you're aware of or have already attended to and I, being a really new user, have yet to find out about?

Also, when the foreign key join for the first table is displayed it's around the wrong way to what we have adopted as a standard at my work place. Could there be another option added to set the order of the named or FK join in the configuration dialog?

thanks

Michael
michaelb
0

Comments

5 comments

Sort by
  • Tilman
    Hi Micheal,

    thanks for posting. We are aware of the 'self join' issue and it will be fixed in version 3.

    I will add your suggestion regarding the order of foreign keys in the ON clause. I take it, your convention is to have the tables in the ON bit the same order as in the FROM ... JOIN part?

    Regards,

    Tilman
    Tilman
    0
  • michaelb
    Hi Tilman,

    Thanks for the prompt reply!

    If you look at the example I put in my first post that should tell you the order I (and the team I program with) prefer to format our join statements as opposed to what I noticed in my trial so far.

    In short the format would be:
    select blah
    from tbl1 as x
    join tbl2 as y on y.col = x.col

    I have to say that it gets better the more I play with it - great job! And the people I work with are impressed also. I'll definitely have a loo at v3 when it comes out!

    Michael
    michaelb
    0
  • void
    Tilman wrote:
    Hi Micheal,

    thanks for posting. We are aware of the 'self join' issue and it will be fixed in version 3.

    I will add your suggestion regarding the order of foreign keys in the ON clause. I take it, your convention is to have the tables in the ON bit the same order as in the FROM ... JOIN part?

    Regards,

    Tilman

    Would this be configurable? I ask because in my case the convention is to use other table (the one at the left of JOIN) first and this table (the one at the right of JOIN) after, that's because more likely you already saw this table, but you won't know what's the other until you scroll to the right
    void
    0
  • michaelb
    Hey void

    I guess it's just a matter of preference as sql server will probably convert your code internally into the sql 92 standard anyway when it compiles it into the database.

    To me it makes more sense to use the version I posted previously, so an option to present it this way in the config screen would make people on both sides of the fence happy.

    Michael
    michaelb
    0
  • void
    Hi, michaelb.

    I agree, this is just a matter of preferences (it always is, just like "new line brackets"). Also, as for the notation you suggested. I think that's actually the more natural way (I sometimes use it myself), and some times people tend to get unconfortable with the other way around, just like the code below 
    if ( SOME_CONSTANT == letsUseAnInsanelyVeryLongVariableOrWhateverName ) {
    crashOS();
}
    The good point on this is that I can mistype and use "=". and nothing happens (well, just the compiler ranting at me) Anyway, still nothing changes, it's a matter of taste :)
    void
    0

Add comment

Please sign in to leave a comment.