T-SQL Problem - should I use the "ALL" clause?

[google]

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_Int ermediary

CaseID Intermediary_Ty pe
---------------- ----------------------------
101010101 73
101010101 123
101010101 90
202020202 75
202020202 90
303030303 90
303030303 73

TABLE 2: Intermediary_gr ouping

Relationship_ty pe Intermediary_Ty pe
-------------------------- ---------------------------
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_Int ermediary 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_gr ouping. 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_ty pe 1, 202020202 into relationship_ty pe 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

[David Portas]

go****@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_Int ermediary

CaseID Intermediary_Ty pe
---------------- ----------------------------
101010101 73
101010101 123
101010101 90
202020202 75
202020202 90
303030303 90
303030303 73

TABLE 2: Intermediary_gr ouping

Relationship_ty pe Intermediary_Ty pe
-------------------------- ---------------------------
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_Int ermediary 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_gr ouping. 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_ty pe 1, 202020202 into relationship_ty pe 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_Int ermediary (CaseID INT NOT NULL,
Intermediary_Ty pe INT NOT NULL, PRIMARY KEY
(CaseID,Interme diary_Type));

CREATE TABLE Intermediary_gr ouping (Relationship_t ype INT NOT NULL,
Intermediary_Ty pe INT NOT NULL, PRIMARY KEY
(Relationship_t ype,Intermediar y_Type));

INSERT INTO Application_Int ermediary (CaseID, Intermediary_Ty pe)
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_gr ouping (Relationship_t ype,
Intermediary_Ty pe)
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_Int ermediary AS A
JOIN Intermediary_gr ouping AS I
ON A.Intermediary_ Type = I.Intermediary_ Type
GROUP BY A.CaseID, I.Relationship_ type
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM Intermediary_gr ouping
WHERE Relationship_ty pe = 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/m...S,SQL.90).aspx
--

[google]

That's brilliant, solves my first question, and has taught me something
I didn't know yesterday.

But here's another one to test you with :-)

What could I do to identify the third case, which doesn't match the
criteria? I figured I'd put the results of the query you've helped me
with into a table, then do an unmatched between that and the original
case list.... which will work, but seems a bit "belt and braces". I'm
sure there must be a more elegant solution out there.

Cheers

Lee

[Hugo Kornelis]

On 20 Jun 2006 23:58:29 -0700, go****@the-gallowgate.com wrote:

That's brilliant, solves my first question, and has taught me something
I didn't know yesterday.

But here's another one to test you with :-)

What could I do to identify the third case, which doesn't match the
criteria? I figured I'd put the results of the query you've helped me
with into a table, then do an unmatched between that and the original
case list.... which will work, but seems a bit "belt and braces". I'm
sure there must be a more elegant solution out there.

Hi Lee,

Same idea, but all combined into a single query:

SELECT A.CaseID, D.Relationship_ type
FROM (SELECT DISTINCT CaseID
FROM Application_Int ermediary) AS A
LEFT JOIN (SELECT A.CaseID, I.Relationship_ type
FROM Application_Int ermediary AS A
INNER JOIN Intermediary_gr ouping AS I
ON A.Intermediary_ Type = I.Intermediary_ Type
GROUP BY A.CaseID, I.Relationship_ type
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM Intermediary_gr ouping AS I2
WHERE I2.Relationship _type = I.Relationship_ type)) AS D
ON D.CaseID = A.CaseID;

Note that unmatched grooups will appear with relationship_ty pe NULL. You
can use a COALESCE function in the first SELECT to change that, if you
wish.

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[--CELKO--]

>> What I need to be able to do is classify these apps into a relationship type, so have set up the second table, Intermediary_gr ouping. 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.<<

Look up "Relationsl Division"; this is one of Codd's original
operators.