Posted by Hugo Kornelis on 06/25/06 11:02

On 20 Jun 2006 23:58:29 -0700, google@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

[Back to original message]