|
|
Posted by David Portas on 06/20/06 20:01
google@the-gallowgate.com wrote:
> 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
Try this. Note my assumptions about the keys.
CREATE TABLE Application_Intermediary (CaseID INT NOT NULL,
Intermediary_Type INT NOT NULL, PRIMARY KEY
(CaseID,Intermediary_Type));
CREATE TABLE Intermediary_grouping (Relationship_type INT NOT NULL,
Intermediary_Type INT NOT NULL, PRIMARY KEY
(Relationship_type,Intermediary_Type));
INSERT INTO Application_Intermediary (CaseID, Intermediary_Type)
SELECT 101010101, 73 UNION ALL
SELECT 101010101, 123 UNION ALL
SELECT 101010101, 90 UNION ALL
SELECT 202020202, 75 UNION ALL
SELECT 202020202, 90 UNION ALL
SELECT 303030303, 90 UNION ALL
SELECT 303030303, 73 ;
INSERT INTO Intermediary_grouping (Relationship_type,
Intermediary_Type)
SELECT 1, 73 UNION ALL
SELECT 1, 123 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 2, 75 UNION ALL
SELECT 2, 90 ;
SELECT A.CaseID, I.Relationship_type
FROM Application_Intermediary AS A
JOIN Intermediary_grouping AS I
ON A.Intermediary_Type = I.Intermediary_Type
GROUP BY A.CaseID, I.Relationship_type
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM Intermediary_grouping
WHERE Relationship_type = I.Relationship_type);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|