SQL - Conditional Selection

Question

I am trying to build an Oracle SQL query where I select users that contain at least one License = '+' while all Default Orgs = '-'. In other words, select users with licenses that have no default organizations. In the example below I would expect only Annete to show in the result.

Table_Users: User, License

Table_Organizations: Default_Org, Org_Name

Query below returns no results:

select User 
from Table_Users, Table_Organizations 
where Table_Users.User = Table_Organizations.UsrX 
and (Default_Org = '+' and Default_Org = '-')*

Table Example

(Default_Org = '+' and Default_Org = '-') ??? (license= '+' and Default_Org = '-') — ScaisEdge, Sep 26 '17 at 19:45

score 0 · Answer 1 · answered Sep 26 '17 at 19:54

0

select User 
from Table_Users, Table_Organizations 
where Table_Users.User = Table_Organizations.UsrX 
and License = '+' and Default_Org = '-'

answered Sep 26 '17 at 19:54

Ted at ORCL.Pro

1,602
1
7
10

While this points out an obvious code issue of the post it doesn't actually answer what he is trying to find which is users that have NO default organization. your answer as currently written would return jeff even though he has a default organization – Matt Sep 26 '17 at 20:07
I beg to differ @Matt, maybe you should run the above with the data provided – Ted at ORCL.Pro Sep 26 '17 at 20:08
1

you are correct with the above data it will return what the OP has said but I would presume in looking at the above data that OP also left out a few cases included Jeff License = '+' and default_org = '-' and if that case is present this query would return Jeff but that would not be desired because another row has a default_org – Matt Sep 26 '17 at 20:13
@Matt I can't guess the rest of the data on the table ;)) – Ted at ORCL.Pro Sep 26 '17 at 20:22
2

You could provide some commentary on what the OP was doing wrong, and/or how you fixed it. Answers aren't only for the benefit of the person asking, they are used for others to learn too. – Graham Sep 26 '17 at 21:43

Matt · Answer 2 · 2017-09-26T20:09:35.490

SELECT
    u.User
FROM
    Table_Users u
    INNER JOIN Table_Organizations o
    ON u.User = o.Usrx
GROUP BY
    u.User
HAVING
    COUNT(CASE WHEN License = '+' THEN 1 END) > 0
    AND COUNT(CASE WHEN Default_Org = '+' THEN 1 END) = 0

First I would suggest using Explicit not Implicit join, it is a pet peeve of mine and I think a number of others on this site as Explicit join has been part of the ANSI SQL standard for a lot of years now.

As far as what technique you actually want would be called conditional aggregation. By counting only the values you are looking for you can then use them in a HAVING clause to exclude the record(s) you don't want.

Note COUNT(CASE WHEN... THEN END) will work because only the value you want will have a value to be counted and anything not meeting that condition will be NULL and therefore not counted.

Because I don't know which table has License on it you could also potentially use exists as follows:

SELECT
    u.User
FROM
    Table_Users u
WHERE
    u.License = '+'
    AND NOT EXISTS(SELECT 1 FROM Table_Organizations o WHERE u.User = o.Usrx AND Default_Org = '+')

score 0 · Answer 3 · answered Sep 26 '17 at 20:14

Knowing + comes before - we can use a min aggregate. This assumes license and default org could only have '+' or '-' values.

With cte ("user", license, default_org, org_name) as  (
SELECT 'Jeff','+','+', 'Org 1' FROM DUAL UNION ALL
SELECT 'Jeff','-','-', 'Org 2' FROM DUAL UNION ALL
SELECT 'Jeff','-','-', 'Org 3' FROM DUAL UNION ALL
SELECT 'Annete','+','-', 'Org 4' FROM DUAL UNION ALL
SELECT 'Annete','-','-', 'Org 5' FROM DUAL)

SELECT "user", min(license), Min(default_org) 
FROM CTE A
GROUP BY "user"
HAVING min(license) = '+' 
   AND min(Default_org) = '-';

If license and default_org both have indexes with user; this would be pretty fast.

SQL - Conditional Selection

3 Answers3