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

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)

CodePudding user response：

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

ID_CARD	CARD_NUMBER	CARD_VALUE
1	11111	621
4	33333	621

db<>fiddle here

CodePudding user response：

If 621, 622 can be in in the same field then:

WHERE card_value NOT LIKE 'b2%

If they're separate rows, I'm going to make an assumption that there's a unique identifier for each client since there's no sample data in the OP.

SELECT *
FROM t1
WHERE unique_client_id NOT IN (SELECT unique_client_id FROM t1 where card_value = 622)