How to leave in the selection only those values that match on one line?

Question

There is a table that stores information about the card.

id_card, card_number, card_value

When a client issues a card, he has a choice: digital card or digital with plastic

When issuing a digital card, card_value becomes = 621

When issuing a digital card with plastic, card_value writes 2 lines 621 and 622

622 - plastic card

The task is to display only digital cards, with card_value = 621

But when I select digital cards without plastic (621), then both digital cards and digital cards with plastic are included in the selection.

This is logical because the value of the field actually contains 621 and 622.

But how to make it so as not to show records in the selection if 621 and 622 are found together?

I suppose this can be achieved with some extra column like id_card, but I doubt that's correct.

I use SQL (ORACLE 12)

Answer 1

Assuming that card_number will be the same for both the 621 and 622 rows then:

SELECT id_card,
       card_number,
       card_value
FROM   (
  SELECT t.*,
         MAX(card_value) OVER (PARTITION BY card_number) AS max_card_value
  FROM   table_name t
  WHERE  card_value IN (621, 622)
)
WHERE  max_card_value = 621;

Which, for the sample data:

CREATE TABLE table_name (id_card, card_number, card_value) AS
SELECT 1, 11111, 621 FROM DUAL UNION ALL
SELECT 2, 22222, 621 FROM DUAL UNION ALL
SELECT 3, 22222, 622 FROM DUAL UNION ALL
SELECT 4, 33333, 621 FROM DUAL UNION ALL
SELECT 5, 33333, 555 FROM DUAL UNION ALL
SELECT 6, 44444, 621 FROM DUAL UNION ALL
SELECT 7, 44444, 622 FROM DUAL;

Outputs:

ID_CARD	CARD_NUMBER	CARD_VALUE
1	11111	621
4	33333	621

db<>fiddle here