How to use a self join on this table instead of the subquery being used when a concatenation is taking place?

Question

I have the following query

Select DISTINCT a.code,a.Code_group,a.value,a.code_auth FROM AEO.CODE_CM a WHERE a.CODE_GROUP IN 
(SELECT CONCAT (b.code,'_PART') FROM AEO.CODE_CM b WHERE b.CODE_GROUP ='EPE_AUTHTYPE');

So based on the code_group value it receives it picks each code and concatenates the string to it and returns it as a code_group. I need to avoid using subquery to make it less complex and since its the same table I tried to use self join as

Select DISTINCT a.code,a.Code_group,a.value,a.code_auth FROM AEO.CODE_CM a INNER JOIN  AEO.CODE_CM b 
ON a.code_group =b.code_group WHERE a.code_group = CONCAT(b.code,'_PART') AND b.code_group ='EPE_AUTHTYPE';

But I am getting a blank result in this case

Answer 1

The queries are not equivalent, since you have wrong additional a.code_group = b.code_group predicate in the 2-nd query.
It should be something like this:

WITH AEO_CODE_CM (CODE, CODE_GROUP) AS
(
VALUES 
  ('code1', 'EPE_AUTHTYPE')
, ('code2', 'code1_PART')
)

Select DISTINCT a.code, a.Code_group 
FROM AEO_CODE_CM a
/* 
WHERE a.CODE_GROUP IN 
(
SELECT CONCAT (b.code,'_PART') 
FROM AEO_CODE_CM b 
WHERE b.CODE_GROUP ='EPE_AUTHTYPE'
)
*/
--/*
JOIN AEO_CODE_CM b ON a.code_group = CONCAT(b.code,'_PART') 
WHERE b.code_group ='EPE_AUTHTYPE'
--*/
;