JOIN ON support?

• 
  One more thing... If I explicitly join a table I no longer have support for just the fields I wanted case in point:
  
  
  
  SELECT A.Field1, A.Field2, B.Field1
  From TableA AS A INNER JOIN TableB AS B ON A.FieldPK = B.FieldPK
  
  
  When I type B. SqlPrompt2 would list me a set of fields related only to Table B if I did A. I would get a list of only fields in tableA, Sql Prompt3 doesn't seem to do that, it seems to give me all the fields wich is not useful at all.
  
  
  Thanks,

  fhtapia December 14, 2006 18:38

  - 0 +
• 
  Hi,

  Hi, I just started using Sql Prompt 3 after finding the announcement, I've been using Sql Prompt for at least 6 months now and I really love it. Sql Prompt 2 had support for the ON clause where I could type INNER JOIN ON and it would drop down a set of matching IDs which was very useful... any idea if this will integrate into sql prompt 3?

  This should be working. We always suggest join conditions based on foreign keys. There are also options to get join conditions based on column name and column type. Could you give me a specific example where this isn't working?

  SELECT A.Field1, A.Field2, B.Field1
  From TableA AS A INNER JOIN TableB AS B ON A.FieldPK = B.FieldPK
  
  
  When I type B. SqlPrompt2 would list me a set of fields related only to Table B if I did A. I would get a list of only fields in tableA, Sql Prompt3 doesn't seem to do that, it seems to give me all the fields wich is not useful at all.

  Again, I can't reproduce this.
  
  Could you tell me a bit about your set up? I.e. what editor are you using, what version is it? SQL Server version and language? What's your OS/language?
  
  Regards,
  
  Tilman

  Tilman December 18, 2006 13:14

  - 0 +
• 
  I can show you one version that doesn't work...
  
  First, the following does work:
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e ON --***
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  
  ... type space before the *** comment and you get joins between "e" and "ea" ... one comment here -- I'd like to see the joins go BOTH ways if possible. I actually prefer doing <table I'm joining to>.col = <table I'm joining from>.col. A small semantic difference, but just the way I like to write my SQL.
  
  Now, a version that doesn't work... did you know that the ON clause can go in one of two places?
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  ON --***
  
  Hit space before the *** comment and you'll get a column list rather than JOIN candidates. The ONs here are nested in the same order as the JOINs, so you should get a list of candidates for the JOIN between "e" and "ea". (Note, I absolutely *hate* this syntax, but I see it from time to time so I suppose it should be supported)
  
  Now, another weird thing... What if I accidentally leave the ON below the JOINs?
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e ON --***
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  ON
  
  Again, hit space before the *** comment and it's broken... Until I do something like:
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e ON --***
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  ON x
  
  Some little bug in the parser, it seems?

  amachanic December 18, 2006 15:22

  - 0 +
• 
  I too would like to be able to specify the table that gets referenced first in the ON statement.
  

  I'd like to see the joins go BOTH ways if possible. I actually prefer doing <table I'm joining to>.col = <table I'm joining from>.col. A small semantic difference, but just the way I like to write my SQL.

  JDS December 18, 2006 16:25

  - 0 +
• 
  Hi,
  

  I'd like to see the joins go BOTH ways if possible. I actually prefer doing <table I'm joining to>.col = <table I'm joining from>.col. A small semantic difference, but just the way I like to write my SQL.

  This has already been suggested, and it's on our list, but I don't think it will make it into v3.

  Now, a version that doesn't work... did you know that the ON clause can go in one of two places?
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  ON --***
  
  Hit space before the *** comment and you'll get a column list rather than JOIN candidates. The ONs here are nested in the same order as the JOINs, so you should get a list of candidates for the JOIN between "e" and "ea". (Note, I absolutely *hate* this syntax, but I see it from time to time so I suppose it should be supported)

  I actually didn't know this. Thanks for pointing it out. I've added an issue to our system.
  

  Now, another weird thing... What if I accidentally leave the ON below the JOINs?
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e ON --***
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  ON
  
  Again, hit space before the *** comment and it's broken... Until I do something like:
  
  SELECT *
  FROM [HumanResources].[EmployeeAddress] ea
  JOIN [HumanResources].[Employee] e ON --***
  JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID]
  ON x

  This is kind of expected behaviour. Our parser doesn't work very well with invalid SQL (which is kind of ironic, i know, but hey...), so as a rule of thumb, the more valid the SQL the better Prompt will work.
  
  Regards,
  
  Tilman

  Tilman December 19, 2006 12:34

  - 0 +

Add comment

Please sign in to leave a comment.