|
|
Posted by google on 06/20/06 19:39
Hi
Hope someone can help me with this - have been staring at this problem
all day, and with the cold I've got, just don't seem to be able to
figure it out!
I've got two tables, here with some test data - sorry, can't create DDL
as I'm at home & can't access the server :-(
TABLE 1: Application_Intermediary
CaseID Intermediary_Type
---------------- ----------------------------
101010101 73
101010101 123
101010101 90
202020202 75
202020202 90
303030303 90
303030303 73
TABLE 2: Intermediary_grouping
Relationship_type Intermediary_Type
-------------------------- ---------------------------
1 73
1 123
1 90
2 75
2 90
Here's what they do, in english. We get applications which can come
under a number of different sourcing relationships. One record is
posted to Application_Intermediary per company involved in the deal, so
you get a record telling you the application number (CaseID) and the
type of intermediary. You are likely, therefore, get more than one
record per application.
What I need to be able to do is classify these apps into a relationship
type, so have set up the second table, Intermediary_grouping. This
would let me know that, should an application have introducers of type
73 AND 90 AND 123 attached to it, it was introduced under relationship
type 1.
So in the examples above, case 101010101 would fall into
relationship_type 1, 202020202 into relationship_type 2 - and 303030303
would not be classified.
Have tried a variety of subqueries, using ANY/SOME/ALL/IN all with no
success. The only way I can think of doing this is to create a
temporary table with one record per application & a concatination of
the intermediary types (e.g. '101010101', '73-123-90'. I have written a
function to do this, but it takes a LONG time to execute.
There has to be a simple way of doing this, that I'm overlooking.
Any ideas would be gratefully received - I'm stuck!
Thanks
Lee
Navigation:
[Reply to this message]
|