Re: General SQL question
On 28 Sep 2005 10:10:52 -0700, andris_sh@yahoo.com <andris_sh@yahoo.com> wrote: > This question is not directly related to Informix, more a generic one. > Still, I always had received very good help in this ng. > > Thing is, I am responsible for database design in one DWH solution. It > is quite complicated, about 100 tables and growing. Recently analysts > decided that, in regards of latest business analysis requirements, DWH > has to contain reference table between two tables, as they are in > many-to-many relationship. > > Now, question is - how to fill keys in this reference table? > Data load process at first fills data in respective tables, then > updates foreign keys between tables and theh time would be to fill > reference table, which contains only two fields, foreign keys to two > other tables. > > I have no idea, how to write procedure, which would fill and update > reference table, can you suggest some exapmle SQL script? Thanks! I suspect you've not received any answers because it is hard to work out your problem. However, it appears that you have two tables, TableA and TableB, such that each record in TableA can be associated with an arbitrary number of rows from TableB and similarly in reverse. You know how to load TableA and TableB. Your problem is that you can't see how to load an association table TableC which relates (associates) a row in TableA with a row in TableB? There is no automatic way to determine such associations. Suppose, for concreteness, that TableA is customer orders and TableB is items for sale. There is no a priori method that can tell you that Order 12345 is supposed to be associated with items 2345 and 3456; you have to know. After all, Order 12345 could just as easily be for items 9876 and 8765 and 7654 - that's the beauty and treachery of a many-to-many association. And, typically, items 2345 and 3456 can be supplied in response to many different customer orders. In this example, you have to have an external mechanism that determines which items are supplied for each order - the DBMS does not have that information available. You will have to adapt my example to your scenario - but something as to tell you about the associations. The DBMS can only really guess; it would probably guess at a cartesian product - which means each row in TableA would be associated with each row in TableB. However, that is incredibly unlikely to be the correct answer. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list