By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jun 20 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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_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/m...S,SQL.90).aspx
--

Jun 20 '06 #2

P: n/a
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

Jun 21 '06 #3

P: n/a
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_Intermediary) AS A
LEFT JOIN (SELECT A.CaseID, I.Relationship_type
FROM Application_Intermediary AS A
INNER 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 AS I2
WHERE I2.Relationship_type = I.Relationship_type)) AS D
ON D.CaseID = A.CaseID;

Note that unmatched grooups will appear with relationship_type 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
Jun 25 '06 #4

P: n/a
>> 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.<<

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

Jun 25 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.