CODEWITHSUNDEEP
mysqlcsv
Kevin Rave
Kevin Rave

Reputation: 14426

Any issue with this MySQL Query

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

Upvotes: 0

Views: 63

Answers (4)

Marty McVry
Marty McVry

Reputation: 2856

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

Upvotes: 1

gezdy
gezdy

Reputation: 3322

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,',%')))

Upvotes: 1

nico gawenda
nico gawenda

Reputation: 3748

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

Upvotes: 1

Anton I. Sipos
Anton I. Sipos

Reputation: 3613

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

Upvotes: 2

Related Questions