Problem with Postgresql, LIKE gives double results-CodePudding

I am currently working with Postgresql and I am facing a problem.

I have two tables "question" and "question_detail" in which there are codes. In "question_detail" are the codes including subcode so e.g. TB01Q07, TB01Q07a, TB01Q08_SQ002. Now I wanted to use the command LIKE to see if the table "question" also contains these records. But in "question.code" there are only codes without the following underscore. This is a table that was given to me, I find this somehow very stupid.

The problem is that when I search with LIKE the value TB01Q07a is listed twice. This is also understandable to me, since searching for TB01Q07% also returns the value TB01Q07a.

Does anyone know of a way to search only for TB01Q07a without it resulting in TB01Q07% as TB01Q07a?

Command

SELECT qd.code, qd.label, q.type
FROM public.question q, 
     public.question_detail qd
where CASE 
         WHEN qd.code = q.code THEN qd.code = q.code 
         ELSE qd.code like CONCAT(q.code,'%') 
      END;

question

| code     | type    |
| ---------|-------- |
| TB01Q07  | comment |
| TB01Q07a | comment |
| TB01Q08  | option  |

**question_detail**

```none
| code           | label |
| -------------- | ------|
| TB01Q07        | AB01 |
| TB01Q07a       | AB02 |
| TB01Q08_SQL002 | AB03 |

I ran the SQL and wanted the TB01Q07a value to appear only once and not be listed twice.

CodePudding user response：

I think I have found a solution with distinct on.

SELECT distinct on (qd.code) q.id_question,qd.code, q.question, q.question_type
FROM public.question q, public.question_detail qd
where qd.code like CONCAT(q.code,'%');

CodePudding user response：

like('TB01Q07%') matches both TB01Q07 and TB01Q07a, so you get two rows for TB01Q07 and one row for TB01Q07a.

You need to be more precise and include the underscore. Also make sure it's escaped, _ means any one character in a like.

There is no need for a case, use or. Avoid using multiple from statements, use an explicit join with an explicit on. This is clearer and gives you more control over the join.

select qd.*, q.*
from public.question q
join public.question_detail qd
  on qd.code = q.code OR qd.code like q.code || '\_%'

Demonstration.

Note: this problem doesn't exist if you use foreign keys. Assign unique IDs to question and reference them in question_detail. This is faster, shields you from changes to the question code, and ensures the referred to question exists.