CODEWITHSUNDEEP
mysql
Alexandru Para
Alexandru Para

Reputation: 139

MySQL Select with Subquery

I have 2 table in the database: mainTable and classificationTable:

mainTable:

id  |   classification_id   |   name
---------------------------------------
1   |           1           |   Name1
2   |           2,3,4       |   Name2
3   |           1,4         |   Name3
4   |           4           |   Name4

classificationTable:

classification_id   |   class_name
---------------------------------------
        1           |   Class Name1
        2           |   Class Name2
        3           |   Class Name3
        4           |   Class Name4

I want to get a select for example for row with ID 3 from mainTable like: 

id          =   3
class_name  =   Class Name1, Class Name4
Name        =   Name3

I try this select, but this return only first elemnt from array (fore exempla for row with ID 3, this return only Class Name1) 

SELECT i.*, 
(SELECT GROUP_CONCAT(cl.class_name) FROM classificationTable as cl WHERE cl.classification_id IN(i.classification_id)) as class_name 
FROM mainTable as i;

Help PLZ.

Upvotes: 0

Views: 129

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Yes, you have a poorly designed database. But you can do what you want.

This requires two steps. The first is to join the mainTable to the classificationTable by looking for each classification_id in the list in the mainTable. You can do this using the like operator.

The second step is to bring all the class names back into one column. This is handled using group_concat.

The following query accomplishes this (although it is not tested, so it might have a typo):

select mt.id, mt.name, group_concat(ct.class_name separator ', ')
from mainTable mt join
     classificationTable ct
     on concat(',', classification_id, ',') like concat('%,', ct.classification_id, ',%')
group by mt.id, mt.name

Upvotes: 1

amphibient
amphibient

Reputation: 31212

The problem is your mainTable, which not only violates the 1st normal form but goes even beyond because it concatenates values in the same field (textbook example of a 1NF violation often includes multiple fields, which is bad enough).

The classification_id column should not be a concatenation of values but you should have a new table where you have a separate row for each classification_id and it should be tied into mainTable by id.

E.g.

id  |   classification_id   
---------------------------------------
1   |           1           
2   |           2           
2   |           3         
2   |           4           
3   |           1       
3   |           4     
4   |           4

Once you've done that, your query will be much easier.

Upvotes: 1

Related Questions