In this table for example, I want those groups of id which has 'code' column value as ONLY cod or F. so query should return values of id = B and nothing else. ( Not even values with id = C because id=C also has 'thk' in code and output should have ids with ONLY the mentioned two values)

expected output

CodePudding user response：

You want all rows for the ID of which not exists a forbidden row:

select id, code, value
from mytable
where not exists
(
  select null
  from mytable forbidden_row
  where forbidden_row.id = mytable.id
  and forbidden_row.code not in ('cod', 'F')
);

CodePudding user response：

One of approaches with nested query

SELECT ID,Code, value FROM (
    select ID, Code,
        (SELECT count(*) FROM TableA a where Code = 'cod' and a.ID = TableA.ID) Cod,
        (SELECT count(*) FROM TableA a where Code = 'F' and a.ID = TableA.ID) F,
        (SELECT count(*) FROM TableA a where Code not in ('F','cod') and a.ID = TableA.ID) Other,
        Value
    from TableA 
) SOURCE
WHERE Cod <> 0 AND F <> 0 and Other = 0

CodePudding user response：

We can achieve this using CTE. Check this,

-- Split the two record category first, then check cod Or F condition.
WITH Count2 AS (
    SELECT id
    FROM YourTable
    GROUP BY id
    HAVING COUNT(id) = 2
), 
codORF AS (
    SELECT id, code, COUNT(id) FROM YourTable T1 
    LEFT JOIN Count2 T2 On T1.id = T2.id
    WHERE code = 'cod' OR code = 'F'
    GROUP BY id, code
    Having COUNT(id) = 1
)
--  Finally to take all values
SELECT T1.* 
FROM YourTable T1 
INNER JOIN codORF T2 ON T1.id = T2.id

CodePudding user response：

with main as (
select *, count(id) over(partition by id order by id) as total_rows
from sample
), next_and_before as (
select *,
COALESCE(lag(code) over(partition by id order by id),lead(code) over(partition by id order by id)) as before_next
from main where total_rows <= 2 
)
select * from next_and_before 
where lower(trim(concat(code,before_next)))in('codf','fcod','cod','f')

Its a bit of hacky solution:

• first you are filtering out all the rows that have less than or equal to 2 rows, since there could be cases where you only have one row per id with a code value = 'f' or 'cod', if you don't want that then simply change the last part to: in ('codf','fcod')
• then out of two rows, you are looking at the next and before value and checking if it contains other than 'f' or 'cod'
• where clause will filter those out if they exist

Test Results from the link below:

Results of sample data