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

Navigation
4

I'm trying to run a query on the database to collect all rows from table A that are referenced in a column in table B:

Query I'm 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 lines 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       │
└──────┴───────┴───────┴────────┴────────┴───────┴──────────────┘

Only query table B

If I run the query from 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 will return it to me: 

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

Tables A and B

Below is 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 execute the total query I indicated, instead of receiving the 3 rows from the table_eshop_lines table, I only get the first row that contains the 1 value in the id ?

    
asked by anonymous 24.12.2013 / 20:38

3 answers

3
As far as I know, the IN operator works only for lists, ie if its% of internal% returned more than one line it would look for select on each of those lines - in contrast to the case where < strong> SQL contains the list to be searched in text format - A.id .

I suggest experimenting with A.id in (1,2,3) 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 INSTR ) of the first occurrence of a substring in another string (or 1 if it is not substring). Since its value 0 is a string, then this condition should evaluate true only when B.lines_id is contained in it.

Note: I used A.id to prevent, for example, CONCAT from being found in 1 . This way, 11;12;13 is not found in ;1; .

    
24.12.2013 / 20:57
2

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

However, assuming you can not 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))
    
25.12.2013 / 20:12
0
select * 
from a 
where FIND_IN_SET(a.id,
                       SELECT REPLACE(B.lines_id, ';', ',') AS ids
                        FROM table_eshop B
                  );
    
25.12.2013 / 21:43
What is the equivalent of UserControl in ASP.NET MVC? What is the most efficient way to calculate the HashCode of an object in Javascript?