Any issue with this MySQL Query

Question

I have this query:

   SELECT A.id FROM TableB B
   LEFT JOIN TableA A ON 
          CONCAT(',',B.class_id,',') LIKE CONCAT('%,',A.class_id,',%')
   WHERE A.class_id is not null

TableA

 [id]   |   [class_id]
---------------------
 One       1, 10, 16, 18
 Two       14, 11
 Three     19, 13, 15
 Four      10

TableB

 [id]   |   [class_id]
---------------------
 ABC         1
 AC          1
 DE          10 
 DEC         19
 ACD         16
 BCD         18
 BCO         18

I am not getting all the ids from TableA that has the class_id from TableB. And I am open to any other better query suggestions as well.

This is what I want:

  One   // class_id contains  1, 10 16 and 18 that are in `TableB` class_id
  Three // class_id contains 19 which is in `TableB` class_id
  Four  // class_id contains 10 which is in `TableB` class_id

But since class_id from TableB matches class_id in TableA, why even use a join? — Marty McVry, Jan 23 '13 at 22:12
In `TableA` `class_id` is csv, while its a single value column in `TableB` — Kevin Rave, Jan 23 '13 at 22:13
@gezdy I just want to get `id` from `TableA` that has all the `class_id` from `TableB` in its `class_id` column. — Kevin Rave, Jan 23 '13 at 22:17
It looks like you first need to DE-CONCAT you input in tableA and then query tableB with the result. However, de concat is not a function in MYSQL. — Mr. Radical, Jan 23 '13 at 22:36

score 2 · Answer 1 · answered Jan 23 '13 at 22:28

2

While you might get this strategy to work, it will be tricky and a very slow query. The trouble will be with numbers that appear at the start or end of the csv lists, and thus won't match the pattern '%,X,%'

What you should do is make a proper table with one row per (id, class_id), like so:

[id]   |   [class_id]
---------------------
One       1
One       10
One       16
One       18
Two       14
Two       11
Three     19
Three     13
Three     15
Four      10

Then your query becomes a plain join:

SELECT A.id, B.class_id FROM TableB B
join TableA A ON 
      B.class_id = A.class_id
where A.class_id is not null

answered Jan 23 '13 at 22:28

Anton I. Sipos

3,493
3
27
26

I understand. We can make it work with a tweak to the database. But its not an option now. – Kevin Rave Jan 23 '13 at 22:31
1

If you can't alter your DB now, and performance isn't critical, I'd recommend reading the data into your application and doing this operation in your application's language rather than SQL. This kind of operation is an ill-fit for SQL, but easy in most general purpose languages. Are the tables small enough to fit in memory? – Anton I. Sipos Jan 23 '13 at 22:34

gezdy · Answer 2 · 2013-01-23T22:39:40.003

1

This should work (edited):

select A.id from TableA A where A.id not in (
SELECT distinct A2.id FROM TableA A2 where 
    not exists (select B.id from TableB B where CONCAT(',',B.class_id,',') 
                         like CONCAT('%,',A2.class_id,',%')))

edited Jan 23 '13 at 22:39

answered Jan 23 '13 at 22:28

gezdy

3,292
2
14
14

ok, there was an error, I re edited it, try again please now. It should work now! :-) – gezdy Jan 23 '13 at 22:40

score 1 · Accepted Answer · answered Jan 23 '13 at 22:38

1

It looks like you just mixed up the search strings:

CONCAT(', ',B.class_id,',') LIKE CONCAT('%, ',A.class_id,',%')

should be

CONCAT(', ',A.class_id,',') LIKE CONCAT('%, ',B.class_id,',%')

Because you are looking for occurrences of B in A.

Also, take care of the spaces after the colons when concatenating them

answered Jan 23 '13 at 22:38

nico gawenda

3,648
3
25
38

True, but won't work if the id appears at the start or end of the list. You could perhaps scrub the DB to ensure the commas appear at both the beginning and end of the list in every row, then this strategy would work. – Anton I. Sipos Jan 23 '13 at 22:40
1

@Anton: Because of this he is concatenating them, so it works also with IDs at beginning or end. – nico gawenda Jan 23 '13 at 22:44

score 1 · Answer 4 · answered Jan 23 '13 at 22:52

1

SELECT a.id, b.class_id
FROM TableA a, TableB b
WHERE CONCAT(', ',a.class_id,', ') LIKE CONCAT('%, ',b.class_id,', %');

You actually don't need the a.class_id is not null... Because then the string from b.class_id won't be in a.class_id.

SQL Fiddle

answered Jan 23 '13 at 22:52

Marty McVry

2,838
1
17
23

Any issue with this MySQL Query

4 Answers4