How to select rows from table A that are referenced in a column of table B?

Question

I am trying to execute a database query to collect all rows in table A referenced in a column of table B:

Query I am running

-- Consulta a recolher da tabela A as linhas com ID referenciado na tabela B
SELECT A.*
FROM table_eshop_lines A
WHERE A.id IN (
    SELECT REPLACE(B.lines_id, ';', ',') AS ids
    FROM table_eshop B
    WHERE B.id=1
)

The expected result would be 3 lines, namely line 1, 2 and 3. What happens is that I only get line 1:

┌──────┬───────┬───────┬────────┬────────┬───────┬──────────────┐
│  id  │  pid  │  ref  │  name  │  isbn  │  qtd  │  unit_price  │
├──────┼───────┼───────┼────────┼────────┼───────┼──────────────┤
│ 1    │ 254   │       │ John   │        │ 1     │  25.08       │
└──────┴───────┴───────┴────────┴────────┴───────┴──────────────┘

See only table B

If you run the query of the second table, I get the desired values:

-- Consulta a tabela B
SELECT REPLACE(B.lines_id, ';', ',') AS ids
FROM table_eshop B
WHERE B.id=1

You’re gonna give it back to me:

┌──────────┐
│   ids    │
├──────────┤
│  1,2,3   │
└──────────┘

---

Tables A and B

Below follows the description of each of the tables obtained through Mysql DESCRIBE ^(English):

DESCRIBE `table_eshop_lines` 
┌────────────┬───────────────┬──────┬───────┬─────────┬────────────────┐
│ Field      │ Type          │ Null │  Key  │ Default │ Extra          │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ id         │ int(11)       │ NO   │  PRI  │ NULL    │ auto_increment │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ pid        │ int(11)       │ NO   │       │ NULL    │                │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ ref        │ varchar(200)  │ NO   │       │ NULL    │                │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ name       │ varchar(500)  │ NO   │       │ NULL    │                │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ isbn       │ varchar(500)  │ NO   │       │ NULL    │                │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ qtd        │ int(11)       │ NO   │       │ NULL    │                │
├────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ unit_price │ decimal(10,2) │ NO   │       │ NULL    │                │
└────────────┴───────────────┴──────┴───────┴─────────┴────────────────┘

DESCRIBE `table_eshop` 
┌─────────────┬───────────────┬──────┬───────┬─────────┬────────────────┐
│ Field       │ Type          │ Null │  Key  │ Default │ Extra          │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ id          │ int(11)       │ NO   │  PRI  │ NULL    │ auto_increment │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ sid         │ text          │ NO   │       │ NULL    │                │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ uid         │ int(11)       │ NO   │       │ NULL    │                │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ lines_id    │ text          │ NO   │       │ NULL    │                │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ total_goods │ decimal(10,2) │ NO   │       │ NULL    │                │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ end_time    │ datetime      │ NO   │       │ NULL    │                │
├─────────────┼───────────────┼──────┼───────┼─────────┼────────────────┤
│ status      │ tinyint(1)    │ NO   │       │ NULL    │                │
└─────────────┴───────────────┴──────┴───────┴─────────┴────────────────┘

---

Question:

What am I doing wrong so that when I run the full query I indicated, instead of receiving the 3 rows of the table table_eshop_lines, I only get the first line containing the value 1 in the column id ?

Answer 1

As far as I know, the operator IN works only for lists, i.e. if your select internal return more than one line it would seek the A.id on each of these lines - in contrast to the case where the SQL itself contains the list to be searched in text format - A.id in (1,2,3).

I suggest you try the function INSTR instead:

SELECT A.*
FROM table_eshop_lines A
WHERE INSTR((
    SELECT concat(';', B.lines_id, ';') AS ids
    FROM table_eshop B
    WHERE B.id=1
), concat(';', A.id, ';')) > 0;

Example in Sqlfiddle.

It returns the index (based on 1) of the first occurrence of a substring in another string (or 0 if it is not substring). As its value B.lines_id is a string, so this condition should evaluate true only when the A.id is contained in it.

Note: I used the CONCAT to prevent, for example, 1 was found in 11;12;13. Thus, ;1; is not found in ;11;12;13;.

Answer 2

Its biggest problem is that table B is violating the first normal form, because the field lines_id is a multi-valued field. If you can solve this, your problem becomes much easier.

Anyway, assuming you can’t change the modeling, try this:

SELECT A.*
FROM table_eshop_lines A, table_eshop B
WHERE B.id = 1
AND (A.id = B.lines_id
OR B.lines_id LIKE CONCAT("%;", A.id, ";%")
OR B.lines_id LIKE CONCAT(A.id, ";%")
OR B.lines_id LIKE CONCAT("%;", A.id))

Answer 3

select * 
from a 
where FIND_IN_SET(a.id,
                       SELECT REPLACE(B.lines_id, ';', ',') AS ids
                        FROM table_eshop B
                  );