JOIN Completion Is Not Right

brianorrell

I have been trying to use SQL Prompt 3 and am running into a lot of problems with the JOIN Auto-Completion.

When I type INNER JOIN it pops up a list of FIELDS having nothing to do with any of the foreign key relationships that I have with my tables. What I would expect would be that it would show a list of tables that have foreign key relationships with my previous table. That is not the case.

I'm not sure why this is. I have unchecked all the additional "JOIN Conditions" under the Options dialog. That doesn't appear to help.

Any and all help would be appreciated. I am in eval mode right now and the main reason I wanted the product was for the join auto-completion like it use to have. Without that, I can't imagine using the tool.

[/img]

Bart Read

Hi Brian,


Can you supply me with an example query that doesn't work please?


Thanks,
Bart

brianorrell

If I type in

SELECT
FROM (It prompts me with a list of tables) [Authors]
INNER JOIN (It then prompts me with a list of fields!-- not right, it should prompt me with a list of tables that have foreign key relationships to Authors)


It doesn't matter which database I select, the intellisense is always acting the same. I tried installing the 3.1 release candidate but that didn't improve anything.

Bart Read

Hi Brian,


Suddenly it all becomes clear. Your problem is that, e.g.

SELECT
FROM MyTable1
INNER JOIN

is completely invalid SQL and breaks the parsing because you have no "column list" between the SELECT and the FROM. If you had entered the following:

SELECT *
FROM MyTable1
INNER JOIN

then you'd get the list of tables after the INNER JOIN as expected. Just about anything else in place of the * would also work, but this is probably the easiest thing to do, particularly if you want to expand all the columns in your query.

Note that SELECT FROM with no column list will provide autocompletion if you place the cursor in between the SELECT and the FROM (say if you delete the *), and then if you like you can use the column picker to choose the exact columns you want to insert.

Hope that helps.


Thanks,
Bart

brianorrell

OK, that makes sense with the columns.

But now, it still isn't recommending a table to join to based on the foreign keys...

If I type in

SELECT *
FROM (Prompts for Table list) [Account]
INNER JOIN (Shows a listing of all tables with [Account] as the first listing-- basically, it is just showing the same list it showed when I typed in FROM with no regard to the tables that have foreign key relationships to the Account table)

Is this the normal intellisense? It use to show the tables that had foreign key relationships only.

Bart Read

Hi Brian,


SQL Prompt 3 will allow you to JOIN to any table and at the moment doesn't export an option to limit that simply to tables related by foreign key since for many developers and DBAs this is too limiting. We may introduce another option for this in the future. You can however limit the JOIN conditions that it generates using the Options dialog so that it will only generate such conditions when a foreign key relationship exists.

This may well differ from the version 2 behaviour, however it has to be said that for a variety of reasons when we started with version 3 (which we created from scratch) we very deliberately didn't set out to mimic version 2 in most respects, so I'll admit that there are some differences in behaviour that might take a little getting used to. Hopefully though the overall experience is much better.

I will add a suggestion to our list to only show tables after JOIN that are linked via foreign key relationship to the current table. It might perhaps work better if these tables were shown at the top of the list rather than being the only tables shown. To do this properly would also impact both performance and memory usage, with the performance hit being taken during the indexing step. I don't think the memory hit would be too bad, but without trying it I couldn't say for sure. The performance hit could be fairly significant for a large database though since I think to do this properly we'd need to include tables with foreign key dependencies on the current table as well as those that the current table has foreign keys against - so in both directions.


Thanks,
Bart

brianorrell

So, when I type in JOIN, the tables that are shown to me do not take into account the foreign key relationships that are setup on the tables? In other words, there is no difference between the list of tables I see when I type in FROM and when I later type in JOIN?

That was really the biggest selling point in my opinion for SQL Prompt. I could open a database I had little knowledge of and very easily write SQL very quickly.

I demoed this feature to a lot of my clients who were immediately ready to buy because of that specific functionality.

You guys should definitely re-think adding that in. That was the only reason I wanted to buy SQL Prompt (and possibly get it for the rest of my staff).

Bart Read

OK thanks, it's certainly something we'll consider doing for the next release, but you also have to consider that not everyone wants to work that way because it can be somewhat limiting if you want to join to a table that isn't linked by a foreign key relationship. This is something that is also quite a common scenario.
Thanks,
Bart

brianorrell

Not asking for it to limit to only the tables, just prioritize.

If a database doesn't have foreign key relationships, then it shouldn't affect the listing at all.

But for a database that DOES have foreign key relationships, it would be crazy to think that the JOIN would not be using those tables in a JOIN 99% of the time.

In the same way that you provide options for what shows on JOIN considerations, the same option could be available for the tables displayed on a JOIN.