Table join?

Hi!

I'm wondering if there is a way to perform a "table join" on different tables in IJC. I have several tables with different information on the same compounds and I would love to combine them into one table similar to a SQL "JOIN" command.

The situation is similar to this:

Table 1

Structure | Internal ID | External ID | Biological responses

Table 2

Structure | External ID | Other info

Table 1 is updated rather often and both tables contain compounds that are not found in the other.

I would like to be able to see the responses from table 1 in a view over the structures in table 2.

Kind regards,

Fredrik

Since IJC 3.0 views in the database are supported, so, in principle, you should be able to create a view that provides the join that you need. See here for details about support for database views:

http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/editing_database/editing_entities.html#views

This might address your needs, but notice the limitations of the current implementation described in that document, that might be a problem.

Let us know if you think that will help.

 

Tim

Hi!

My databases are local Derby databases. Is there a way to connect to them and create a view outside IJC?

Also, does the limitation on non-structure entities mean that I can't make a view that's showing a structure field?

Kind regards,

Fredrik

Yes, you can connect to the local (Derby) database from outside IJC, and also from inside IJC using the Database Explorer tool that is included in IJC, but there are currently some problems with the configuration of this in the 3.0.3 version of IJC which we are trying to fix. I'll post instructions for how to use this once these problems are fixed.

The limitation of views not supporting structure tables may be significant for you, but if the data you are concerned about is primarily the non-structural data then it may be OK to create a view of just this data and use this as a related table to the structures table (e.g create a master-detail report). It really depends on your data and how you want to see it.

There may be other options to try. For instance, if you have multiple SD files which contain the same set of compound IDs but different data fields then you might be able to import them into the same table. During import there is the ability to merge teh data based on a common field (e.g. compound ID) so that the fields in a second file are added as extra fields to the exisiting compounds. But again, it all depends on your data and what you want to do with it. 

Tim