Case help

[notadba]

Hi,

Writing a query that needs to evaluate some values to determine which to bring back. Have 2 tables (trimmed down for clarity)

Table 1
Operator_ID
Classcount
primary_class

Table 2
Operator_ID
operclass

The first table is the parent record. For each user (operator_ID) there is a primary security class and a number of secondary ones. The firat table only holds the counter of all classes and the primary class. The second lists all classes against a user including the primary class.

e.g.

Table 1
Operator_ID Classcount Primary_Class
notadba 3 primary

Table 2
Operator_ID oprclass
notadba primary
notadba Secondary1
notadba Secondary2

Looking for results that will exlude the return of the primary class value in the second table where there is a class count of > 1.

eg.
ID classcount primary class other_classes
notadba 3 primary secondary1
notadba 3 primary secondary2

Where there is a classcount of 1, then happy for the class to be returned.

I have tried using this logic which provides a blank value where the primary class from table 1 matches 2- but would prefer to not have a row returned at all in this case..

, CASE(X.CLASSCOUNT) WHEN 1 THEN Y.OPRCLASS ELSE (CASE when Y.OPRCLASS <> X.OPRCLASS THEN Y.OPRCLASS ELSE '' END) END

Any ideas?

 

[notadba]

Should have just kept working on it....

Solution:

Moved the logic to the criteria...

AND ((X.CLASSCOUNT >1
AND Y.OPRCLASS <> X.OPRCLASS)
OR (X.CLASSCOUNT = 1
AND Y.OPRCLASS = X.OPRCLASS))

and it works just fine....