SQL query return no rows

acid	tran_id	tran_date	tran_particular	tran_amt	part_tran_type
ab500	m1	01-01-2022	123:qwe	10	C
ab500	m5	10-01-2022	124:qse	20	C
ab500	m16	11-01-2022	123:pyh	10	D

I have the above table named htd. I am trying to fetch the result where tran_particular is unique before the “:”

Final output looking for:

acid	tran_id	tran_date	tran_particular	tran_amt	part_tran_type
ab500	m5	10-01-2022	124:qse	20	C

But below query returns no rows:

select tran_id||'|'||tran_date||'|'|| TRAN_PARTICULAR||'|'|| tran_amt
||'|'|| part_tran_type from htd a where a.acid ='ab500'  and 
Substr(TRAN_PARTICULAR,1,instr(TRAN_PARTICULAR,':')-1) not in (select Substr(TRAN_PARTICULAR,1,instr(TRAN_PARTICULAR,':')-1) from htd b where b.acid ='ab500'
  and b.tran_id not in( a.tran_id)) order by tran_date;

Answer

koen >CREATE TABLE htd (tran_id, tran_particular)AS
  2  (
  3  SELECT 'm1', '123:qwe' FROM DUAL UNION ALL
  4  SELECT 'm5', '124:qse' FROM DUAL UNION ALL
  5  SELECT 'm16', '123:pyh' FROM DUAL
  6* );

Table HTD created.

koen >WITH tp_unique_vals (tran_particular, cnt) AS
  2  (
  3  SELECT SUBSTR(tran_particular,1,INSTR(tran_particular,':')-1), COUNT(*) 
  4    FROM htd 
  5    GROUP BY SUBSTR(tran_particular,1,INSTR(tran_particular,':')-1)
  6  HAVING COUNT(*) = 1
  7  )
  8  SELECT h.* 
  9    FROM htd h
 10*        JOIN tp_unique_vals u ON SUBSTR(h.tran_particular,1,instr(h.tran_particular,':')-1) = u.tran_particular;

TRAN_ID    TRAN_PARTICULAR    
__________ __________________ 
m5         124:qse            

koen >