MySQL Select with Subquery

Question

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.

where is classification_name in your table ?

echo_Me
– echo_Me

2012-12-03 19:03:04 +00:00
Commented Dec 3, 2012 at 19:03 — echo_Me
– echo_Me, Commented Dec 3, 2012 at 19:03
Sorry, is my mistake. is class_name

Alexandru Para
– Alexandru Para

2012-12-03 19:52:52 +00:00
Commented Dec 3, 2012 at 19:52 — Alexandru Para
– Alexandru Para, Commented Dec 3, 2012 at 19:52

Gordon Linoff · Accepted Answer · 2012-12-03 19:16:03Z

1

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

answered Dec 3, 2012 at 19:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexandru Para Over a year ago

I understand about classification_id in mainTable. Your solution working perfectly. Thanks for help.

amphibient Over a year ago

@AlexandruPara, consider marking this (or another) answer as accepted, your score will go up as well

amphibient · Accepted Answer · 2012-12-03 19:01:56Z

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.

Collectives™ on Stack Overflow

MySQL Select with Subquery

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related