Oracle SQL where to filter multiple rows

Question

I'm sure there is an easy solution to what I'm trying to do but I just can't think through it.

Table A - Columns - ITEMNUM - ITEMNAME

                   1      Test1
                   2      Test2
                   3      Test3
                   4      Test4
                   5      Test5
                   6      Test6 

Table B - columns - ITEMNUM CHAR

                   1      A
                   1      B
                   2      A
                   2      C
                   3      A
                   3      D
                   4      A
                   4      E
                   5      A
                   5      F
                   6      A
                   6      F

What I want to do is

select itemname 
from A, b
where a.itemnum = b.itemnum

and then I only want the rows where char = 'A' but not when there is also B, C, or D with the same itemnum. The itemnum must have a char be 'A' and then it might be something else with the same itemnum but it can't be B,C, or D. Not all itemnum's will have a char with a value of 'A'. Hopefully that makes sense.

Test4, Test5, and Test6 should be the only ones returned.

I have to put this code into another program that won't let me use pl/sql.

Suggestions?

Answer 1

Something like this should work

SELECT a.itemname
FROM a
WHERE EXISTS (
  SELECT 1
  FROM b
  WHERE b.itemnum = a.itemnum
  AND b.char = 'A'
)
AND NOT EXISTS (
  SELECT 1
  FROM b
  WHERE b.itemnum = a.itemnum
  AND b.char IN ('B', 'C', 'D')
)

EDIT: Not sure if that's what you wanted (please share the expected result structure and data, e.g. like you did for tables A & B), but this may be the second take:

SELECT a.itemname, b.char
FROM a
INNER JOIN b
ON b.itemnum = a.itemnum
WHERE EXISTS (
  SELECT 1
  FROM b b_char
  WHERE b_char.itemnum = a.itemnum
  AND b_char.char = 'A'
)
AND NOT EXISTS (
  SELECT 1
  FROM b b_char
  WHERE b_char.itemnum = a.itemnum
  AND b_char.char IN ('B', 'C', 'D')
)

EDIT 2: Look at this SQL Fiddle, to see how it works.

The result is basically:

ITEMNAME | CHAR
---------+-----
Test4    | A
Test4    | E
Test5    | A
Test5    | F
Test6    | A
Test6    | F

Answer 2

my two cents

select itemnum from (
   select a.itemnum, 
       max(case when b.char in ('B','C','D') then 'Y' else 'N' end) bad_check,
       max(case when b.char = 'A' then 'Y' else 'N' end) has_an_a
   from A, b
   where a.itemnum = b.itemnum
   group by a.itemnum
) where has_an_a='Y'
and bad_check='N';

Answer 3

Use a self join on B with NOT EXISTS

select itemname 
from A, b
where a.itemnum = b.itemnum
and not exists( select 1 from b2 where b2.itemnum = b.itemnum and b2.char in ('B', 'C', 'D'));

You might want to start using ANSI join syntax as well:

select itemname 
from A
join b on a.itemnum = b.itemnum
where not exists( select 1 from b2 where b2.itemnum = b.itemnum and b2.char in ('B', 'C', 'D'));

Answer 4

You can use listagg to build a string with all characters per itemname. Then you can filter for rows with A but without B, C or D:

with    list as
        (
        select  ITEMNAME
        ,       listagg("CHAR") within group (order by "CHAR") chars
        from    TableA a
        join    TableB b
        on      a.ITEMNUM = b.ITEMNUM
        group by
                ITEMNAME
        )
select  *
from    list
where   chars like '%A%'
        and not regexp_like(chars, '[BCD]')

Example at SQL Fiddle.