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

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?

Are there any constraints? Can "A" character value appear multiple times in table B for a specific ITEMNUM? — Hilarion
– Hilarion, Commented Jun 13, 2018 at 17:50
From your comments on the answers, I'm not sure what should the result be. Could you show how the result rows would look like for your case? — Hilarion
– Hilarion, Commented Jun 14, 2018 at 13:18

Andomar · Accepted Answer · 2018-06-13 17:51:39Z

1

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.

answered Jun 13, 2018 at 17:51

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zynon Putney II · Accepted Answer · 2018-06-13 19:54:54Z

1

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'));

edited Jun 13, 2018 at 19:54

answered Jun 13, 2018 at 17:47

Zynon Putney II

6954 silver badges12 bronze badges

2 Comments

UintahIT Over a year ago

Sorry I may not have been clear in my question, but I think you are close. I need to have select a.itemname, b.char and get back test4 A test4 E test5 A test5 F test6 A test6 F. With this I'm only getting char = A in return

Zynon Putney II Over a year ago

Then remove the b.char = 'A' from the clause. I will update my answer

Hilarion · Accepted Answer · 2018-06-14 13:46:57Z

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

edited Jun 14, 2018 at 13:46

answered Jun 13, 2018 at 17:48

Hilarion

8907 silver badges21 bronze badges

1 Comment

UintahIT Over a year ago

Sorry I may not have been clear in my question, but I think you are close. I need to have select a.itemname, b.char and get back test4 A test4 E test5 A test5 F test6 A test6 F

Peter M · Accepted Answer · 2018-06-13 21:03:36Z

0

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';

answered Jun 13, 2018 at 21:03

Peter M

1925 bronze badges

Collectives™ on Stack Overflow

Oracle SQL where to filter multiple rows

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related