0

I'd like to look for a.id from TableA which is more than 1 b.this_id is IN a.id_list.

example table

TableA as a
id    name  id_list  
1     xyz   /0/1
2     efd   /0/1/2  
3     abc   /0/2/3 
4     pqr   /0/2/3/4

TableB as b
this_id     
2         
3  
4

if we count this_id on tableA,

TableA as a
id    number of this_id  id_list
1     0                  /0/1
2     1                  /0/1/2  
3     2                  /0/2/3 
4     3                  /0/2/3/4

since id 3,4 have more than 1 this_id, I'd like to select id 3,4. but since id_list is string, it's quite tricky for me to do that. Is there any way to implement it?

*I made this sql but apparently not works.

SELECT a.id 
FROM tableA a
WHERE a.id_list IN (select b.this_id from tableB b)
and a.id IN (select b.this_id from tableB b);
Juno J
  • 187
  • 2
  • 11

1 Answers1

1

You can do this using join and aggregation. One way uses find_in_set():

select a.id
from tableA a join
     tableB b
     on find_in_set(b.this_id, replace(a.id_list, '/', ',')) > 0
group by a.id
having count(*) >= 2;

That said, storing multiple values in a string is a bad idea. You should have a separate table with one row per id pair. This is a junction table.

Why is storing ids in a string a bad idea? Well:

  • You should use the right type, and a string is not a number.
  • You should declare foreign key relationships.
  • SQL has poor string functionality.
  • The use of strings generally prevents using indexes and other optimizations.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I got this error Error occurred.SQLSTATE[42S22]: Column not found: 1054 Unknown column 'b' in 'on clause' – Juno J Oct 15 '20 at 23:53
  • it's the place for column not table?! – Juno J Oct 15 '20 at 23:54
  • if i change like this, `find_in_set(b.this_id, replace...)` then I have `timeout undefined` error.. – Juno J Oct 16 '20 at 00:05
  • @JunoJ . . . As the answer explains that your method of storing this data is a very bad idea. – Gordon Linoff Oct 16 '20 at 00:46
  • I understood that point but current DB structure is like that and I cannot promtly edit it. we need investigation for current DB first then we can change it : ) – Juno J Oct 16 '20 at 08:01