473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
4 4120
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
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
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
>> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: steve | last post by:
To gain performance, do I need to index ALL the fields in the where clause. Say we have a query like: select stuff from table where field1=.. and field2=... If field1 selection substantially...
11
by: Summa | last post by:
Hi NG, I have the following problem that I hope you can help me with (MS-SQL server 2000) Imagine a statement like this: "select id, firstname, (select top 1 id from testdata) as testid,...
27
by: Chris, Master of All Things Insignificant | last post by:
I have come to greatly respect both Herfried & Cor's reponses and since the two conflicted, I wanted to get some clarification. My orginal post: Herfried, maybe your example here can get you to...
3
by: Sean Shanny | last post by:
To all, We are running postgresql 7.4.1 on an G5 with dual procs, OSX 10.3.3 server, 8GB mem, attached to a fully configured 3.5TB XRaid box via fibre channel. I think we have run into this...
25
by: metaperl.etc | last post by:
A very old thread: http://groups.google.com/group/comp.lang.python/browse_frm/thread/2c5022e2b7f05525/1542d2041257c47e?lnk=gst&q=for+else&rnum=9#1542d2041257c47e discusses the optional "else:"...
5
by: Dimitri Furman | last post by:
SQL Server 2000 SP4. Hoping to find a logical explanation for a certain behavior. Consider this script: USE pubs GO IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA') PRINT...
5
by: No bother | last post by:
I am using 5.0.26-NT on Windows 2000. I have need to use a reference in the outer from clause in a subquery in the select clause. Consider the following example: Select (select b.baitID from...
8
by: chrisdavis | last post by:
I'm trying to filter by query or put those values in a distinct query in a where clause in some sort of list that it goes through but NOT at the same time. Example: ROW1 ROW2 ROW3 ROW4 ,...
2
by: AWasilenko | last post by:
I can't figure out this problem Im having, I just can't understand why it is ignoring the call I put in. First the code (This is a cherrypy website): import sys, cherrypy, html class Root:...
1
by: mtnbikur | last post by:
Lots of reading out there says you should only place “join criteria” in the join clause. That makes perfect sense. What about other search criteria? I’ve got other criteria, ie. cst_delete_flag =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.