Making the selection list ordering smarter

MichaelG

Hi,
A small (but to me significant) feature request
In the current version the ordering when getting the sql prompt selection list. I think the ordering could be improved.
Specifically in the ON section when doing an inner join it would be better to put constraints at the top of the list. (like it worked in the version 2)
- Improving the smartness in the selection depending on context is a general request not just limited to my ON example.

- A change in this area whould significantly imporve our usage of the product, and our satisfaction with it.

Bart Read

Hi Michael,


I'm not too sure what you mean by this? I'm assuming you mean:

SELECT * FROM Product INNER JOIN Supplier
ON Product.SupplierID = Supplier.SupplierID // this bit here

In which case the JOIN conditions should appear at the top of the list if the cursor is after the ON. It may be that SQL Prompt is unable, with its default settings, to generate any JOIN conditions for the tables/views in question. It might also be that you're trying to JOIN to a system view on SQL Server 2005, in which case at the moment unfortunately SQL Prompt won't be able to generate a JOIN condition for you because the meta-data model does not provide us with the necessary column information.

However, assuming this is not the case, SQL Prompt generates JOIN conditions (by default) based on foreign key relationships, and matching column names. You can also get it to generate JOIN conditions based on matching data types by doing the following:

(i) Click SQL Prompt > Options.
(ii) Click on the Candidates tab.
(iii) Under Join Conditions ensure that "Include matching data types" is checked.

Hope that helps you.


Thanks,
Bart

MichaelG

Hi Bart,
Thanks for a quick reply.
Yes, that's exactly what I mean.
I checked the option you suggested, but it maid no difference...
I made an Example using Microsoft AdventureWorks database:

USE [AdventureWorks]
GO

SELECT *
FROM Product prod
INNER JOIN [ProductSubcategory] sCat
ON

-- My list from SQL Prompt (after writing ON (space) above)
/*
prod.[Name] = sCat.[Name]
prod.[DaysToManufacture] = sCat.[ProductCategoryID]
prod.[DaysToManufacture] = sCat.[ProductSubcategoryID]
prod.[DiscontinuedDate] = sCat.[ModifiedDate]
prod.[FinishedGoodsFlag] = sCat.[Name]
prod.[MakeFlag] = sCat.[Name]
prod.[ModifiedDate] = sCat.[ModifiedDate]
prod.[ProductID] = sCat.[ProductCategoryID]
prod.[ProductID] = sCat.[ProductSubcategoryID]
prod.[ProductModelID] = sCat.[ProductCategoryID]
prod.[ProductModelID] = sCat.[ProductSubcategoryID]
prod.[ProductSubcategoryID] = sCat.[ProductCategoryID]
prod.[ProductSubcategoryID] = sCat.[ProductSubcategoryID] -- this is the constraint join I want first(ish) (13th place (scroll starts at 11)
-- ...
*/

-- I also tried revering the order of the join (subcategory first)
SELECT *
FROM [ProductSubcategory] scat
INNER JOIN Product [Product]
ON scat.[ProductSubcategoryID] = Product.[ProductSubcategoryID] -- 12th place in list

Bart Read

OK that looks suspiciously like an FK based join so just switch off the other JOIN condition types and you'll be good to go.

Hope that helps.


Thanks,
Bart

MichaelG

You're correct,

- That helps
But in my opion even if you hav the other options on the list should be sorted so that the FK:s comes prior to the other guesses, which are weaker and may misslead a user to use a incorrect join option before the design FK join option.
- So, please but on your feature wishlist

thanks alot
Michael

Bart Read

Already done.

Bart Read

Hi Michael,


We've tweaked the completion list ordering in SQL Prompt 3.1 to prefer exact matches for the current filter string. You can grab an early access release by following the instructions at:

http://www.red-gate.com/MessageBoard/vi ... php?t=4487

Hope that helps.


Thanks,
Bart