Hello !! How are you ? I would like to request this functionality for the future. It comes from other DB management and it is very very useful avoiding spending a lot of time querying master tables. When we have foreign keys in the data grid, a link icon appear next to the value:
In addition, if we click on the foreign cell, in a window called "value" appears the master table with all its content but with the record with primary key chosen selected:
I know it is no easy to develop it but for a future could be a big improvement avoiding a lot of time requiring master tables to know the content of foreign keys.
I think for your exact use case, what John showed gets you the closest to what you're asking for.
However, you might find Toad's Master-Detail browsing mode a partial fit for your use case.
See snap below, where I have a child table OW_ORDERS whose foreign keys point to the parent record in table OW_CUSTOMERS. You can choose any parent record in the "master", and its children records show up in the "detail" grid below the master table.
However, I think you're asking for the ability to go the other way around: e.g. to select a child record and display its parent? If so, please confirm, and then we'll let Quest Dev look at the feasibility of this use case.
Yes I knew this functionality too (I am using Toad since several years ago in the office and I would not know what to do without it !!) and as you say is just to do it ar reverse mode. Starting from a select that returns few foreign keys, and clicking on them quickly obtain the parent records (directly in the result grid to do it fast without wasting time opening another tab/browser/running another query/...).
Many thanks in advance for your support and quick answer,
Jordi
Master-Detail Browser is good, but perhaps a faster way to get reverse foreign key data is in the Schema Browser (or describe table windows), by clicking the "Detail Dataset" button. The FK or Reverse FK is found automatically. If there is more than one, you can choose which one you want by a drop-down.
but in order to be faster, I thought that could be nice have this functionality in the data grid. So meanwhile you are working querying tables, in the same data grid can review master fk's. if not, you should open a browse, choose the table, activate the 'Detail Dataset' and choose the correct fk you want see; and after that returns to editor and continue working. It is just to go faster.
For one known table, I proceed in this way you said.
As always, many thanks in advance for your support.
Jordi
The reason that we do this only in the Schema Browser is that we already know about the table there. We know the schema, table name, and foreign key constraints. Then we can go from there.
If we were to add something like this to the Editor, this would be a lot of overhead placed on every SELECT statement. We'd have to:
Parse the SQL and find a table/view/mview/synonym name....if there is only one of them. After all, it could be a complex SQL selecting from multiple objects. In that case, we can't do this.
Run some SQL in the background to determine which kind of object it is and what schema it belongs to
If it's a table, run more SQL to determine if it has any FK constraints
then set up the grid for FK lookup
I think that's too much to put on every SQL that runs in the Editor. Especially since when you know you have a table, you can always F4 on the table name, and do what you need there.
Yes ! you are right John, in fact it seems too much for SQL
I just view this functionality in other DB manager, and I liked too much this functionality that is not present in Toad just to be faster in my job and I decided to write this thread to suggest if it would be possible to include this feature in a future.
Anyway, thanks for your understanding and support.
Also, you said "and only works on Schema Browser, not in an editor grid where we usually create queries with few joins."
Here is a thought on that. Try these steps.
Instead of editor, click CTRL+D. Type in the table name and ENTER
a Describe Table window appears. Click on the data tab. Now, the FK column editing feature is there.
If you wish to refine the SQL with a few joins, you can do so by clicking this button and entering your SQL. You can even just paste in a pre-created SQL as long as the base table is the same. Also, from this window you can save SQL (see up at the top where it says "unnamed SQL" - type in a name).
