Filtering data based on a condition in MySQL-CodePudding

I need some help with SQL Query needed for filtering data in MySQL. I have credit numbers(85552553) or codes in one column and then credit SUBcodes in another column. One credit number can have multiple credit subcodes(09101, 09A03 etc).

The catch is that there are 2 MAIN subcodes: 09101 and 09203 and some of the credit numbers have main codes, some don't. If the credit number appears to have any of the main subcodes(09101 or 09203 or both) and other subcodes(09A03, 09B03, 09C03) only the lines with the main code should be taken into consideration, the lines with the other subcodes shouldn't count.

If the credit number doesn't have a main subcode, but has other codes(09A03, 09B03, 09C03) - then those should be taken into consideration. Basically if it has main subcodes it should check the whole Subcode column to see if it has other subcodes, if it has it only should take the lines with the main subcodes.

For example, credit nm 85552553 - in the first and second line we see that it has main code that should be taken by default, but I need to check the whole column to see if it appears somewhere with other codes, like in line 3,4 and 5 (those should not be considered).

I need a Query based on the above written explanation in order to display the credits.

Here is the example of what should be taken and what not.

Credit Nm	Subcode	YES/NO
85552553	09101	YES
85552553	09203	YES
85552553	09A03	NO
85552553	09B03	NO
85552553	09C03	NO
95523469	09A03	YES
95523469	09B03	YES

Main Codes : 09101 and 09203

Thank you in advance :)

CodePudding user response：

One way can be just get the two sets and select mutually exclusive from them.

select * from 
(select * from cc_tab where subcode in ('09101','09203')) t1,
(select * from cc_tab where subcode not in ('09101','09203')) t2
where t1.Credit_Nm != t2.Credit_Nm;

Fiddle here.

CodePudding user response：

You can join the table with itself to find out if the main codes are present.

For example:

select a.*,
  case when b.credit_nm is null then 'Yes'
       else case when subcode in ('09101', '09203')
                 then 'Yes' else 'No' end
       end as selected
from mytable a
left join (
  select distinct credit_nm
  from mytable
  where subcode in ('09101', '09203')
) b on b.credit_nm = a.credit_nm

Result:

 Credit_Nm  Subcode  selected 
 ---------- -------- -------- 
 85552553   09101    Yes      
 85552553   09203    Yes      
 85552553   09A03    No       
 85552553   09B03    No       
 85552553   09C03    No       
 95523469   09A03    Yes      
 95523469   09B03    Yes

See demo.

CodePudding user response：

I would do this with a UNION, something like:

-- First select the ones that have a "main code" 
SELECT CreditNm
,      Subcode
FROM   YourTable
WHERE  Subcode IN ('09101', '09203')

UNION ALL
-- Append the ones that do not exists with a "main code" 
SELECT CreditNm
,      Subcode
FROM   YourTable TS
WHERE  NOT EXISTS (
                     SELECT NULL
                     FROM   YourTable TNS
                     WHERE  TNS.Subcode IN ('09101', '09203')
                       AND  TNS.CreditNm = TS.CreditNm
                  )