MySQL Select query to fetch record base on list values

Question

I am using MySQL. I have 3 Table as below.

Table : subject_Master

--------------------------
subjectId   | subjectShortName
----------------------------------
1           |   English
2           |   French
3           |   German
4           |   Latin
----------------------------------

Table : class_Master

-----------------------------------
classId     | className
----------------------------------
1           |   Rose
2           |   Dasy
3           |   Lily

Table : subjectAllocation

------------------------------------------
allocationId    |   classId |   subjectId
-------------------------------------------
1               |   1       |   1,2
2               |   2       |   2,3,4
3               |   3       |   1,2,3,4

How I can get SQL result as following, Want to fetch SubjectName for each subjectId in subjectAllocation row

ClassName   |   SubjectName
-------------------------------------------
Rose        |   English,French
Dasy        |   French,German,Latin
Lily        |   English,French,German,Latin

first fix your data structure for subjectAllocation. see **[previous questions answer](http://stackoverflow.com/questions/34378017/delete-duplicated-tags-mysql/34378172#34378172)** for how to normalise it — amdixon, Jan 01 '16 at 05:27
it stores variable length string arrays instead of accessible data. this is why you are having trouble getting the output you want. have a read about **[junction tables](https://en.wikipedia.org/wiki/Junction_table)** ( otherwise known as link tables ) — amdixon, Jan 01 '16 at 05:39
is it possible to loop arrays for each values to fetch it's subjectName from subjectMaster? Can you suggest what should be the data structure? — fresher, Jan 01 '16 at 05:45
data structure should be `subjectAllocation { allocationId integer PK, classID integer FK, subjectID integer FK }`. subjectID is now an integer which is accessible and linkable to its actual subject instead of an inaccessible string list. you will have to do some preprocessing ( as in above linked post ) to get it into this usable form — amdixon, Jan 01 '16 at 05:46
That means, if classId = 1 having two subjectId then there should be 2 records in table instead of one as I am showing in my post? — fresher, Jan 01 '16 at 05:49
correct, two records. each of which is linkable and aggregateable — amdixon, Jan 01 '16 at 05:49
How will it keep DB size low ? Right now you have String instead of Integer . — minatverma, Jan 01 '16 at 05:54
most things are possible in sql, but that doesnt mean it should be done — amdixon, Jan 01 '16 at 05:55
Yeh alter the subject allocation table to have a record for each subject id/classid combination. Make a new table the same as you have for subjectAllocation but make the subjectId and int. Then loop through all the data in your current table and make a seperate record for each id in that subjectId field in the old table. You shouldnt ever put multiple IDs in a string like that because you lose all the benefits of SQL in the first place. + its computationaly slower (which will be a massive problem when you get lots of data) And you will have problems if your strings get too long. Use Link Table — Daryl B, Jan 01 '16 at 05:59
Check this http://stackoverflow.com/questions/19073500/sql-split-comma-separated-row?lq=1 . I will go on record to say that your SubjectAllocation table is badly designed for purpose. Please break into rows . — minatverma, Jan 01 '16 at 05:59

Saharsh Shah · Accepted Answer · 2016-01-01T06:05:57.827

5

Use FIND_IN_SET() function:

Try this:

SELECT A.allocationId, 
       B.className, 
       GROUP_CONCAT(C.subjectShortName) AS subjectName
FROM subjectAllocation A
INNER JOIN class_Master B ON A.classId = B.classId 
INNER JOIN subject_Master C ON FIND_IN_SET(C.subjectId, A.subjectId) 
GROUP BY A.allocationId;

edited Jan 01 '16 at 06:05

answered Jan 01 '16 at 05:59

Saharsh Shah

28,687
8
48
83

Thanks a lot. A question.. is this "FIND_IN_SET" has performance issue? I hope its not as its inbuilt function – fresher Jan 01 '16 at 06:08
@TanushPal It's inbuilt function of mysql. – Saharsh Shah Jan 01 '16 at 06:21

score 1 · Answer 2 · edited May 23 '17 at 12:04

plan

fix subjectAllocation so it is accessible & linkable

use sequence generator ( from digits_v ) to unroll variable length string into structured data

access the structured data in simple way, joining on indexed fields

setup

create table subject_Master
(
  subjectId integer primary key not null,
  subjectShortName varchar(23) not null
);

create table class_Master
(
  classId integer primary key not null,
  className varchar(23) not null
);

create table subjectAllocation_inaccessible
(
  allocationId integer primary key not null,
  classId integer not null,
  subjectId varchar(32) not null,
  foreign key ( classId ) references class_Master ( classId )
);

create table subjectAllocation
(
  allocationId integer primary key not null,
  classId integer not null,
  subjectId integer not null,
  foreign key ( classId ) references class_Master ( classId ),
  foreign key ( subjectId ) references subject_Master ( subjectId )
);

insert into subject_Master
( subjectId, subjectShortName )
values
( 1           ,   'English' ),
( 2           ,   'French'  ),
( 3           ,   'German'  ),
( 4           ,   'Latin'   )
;

insert into class_Master
( classId, className )
values
( 1           ,   'Rose' ),
( 2           ,   'Dasy' ),
( 3           ,   'Lily' )
;

insert into subjectAllocation_inaccessible
( allocationId, classId, subjectId )
values
( 1               ,   1       ,   '1,2'      ),
( 2               ,   2       ,   '2,3,4'    ),
( 3               ,   3       ,   '1,2,3,4'  )
;

fix subjectAllocation

create view digits_v
as
SELECT 0 AS N 
UNION ALL 
SELECT 1 
UNION ALL 
SELECT 2 
UNION ALL 
SELECT 3 
UNION ALL 
SELECT 4 
UNION ALL 
SELECT 5 
UNION ALL 
SELECT 6 
UNION ALL 
SELECT 7 
UNION ALL 
SELECT 8 
UNION ALL 
SELECT 9
;

insert into subjectAllocation
( allocationId, classId, subjectId )
select @row_number := @row_number + 1 as allocationId, sa.classId, substring_index(substring_index(sa.subjectId, ',', n.n), ',', -1) sub
from subjectAllocation_inaccessible sa
cross join
(
  select a.N + b.N * 10 + 1 n
  from digits_v a
  cross join digits_v b
  order by n
) n
cross join ( select @row_number := 0 ) params
where n.n <= 1 + (length(sa.subjectId) - length(replace(sa.subjectId, ',', '')))
;

simplicity of access

select c.className, group_concat(s.subjectShortName)
from subjectAllocation sa
inner join class_Master c
on sa.classId = c.classId
inner join subject_Master s
on sa.subjectId = s.subjectId
group by c.className
;

here the join to class_Master can use the primary index ( subjectId )

output

+-----------+----------------------------------+
| className | group_concat(s.subjectShortName) |
+-----------+----------------------------------+
| Dasy      | French,German,Latin              |
| Lily      | German,Latin,English,French      |
| Rose      | English,French                   |
+-----------+----------------------------------+

sqlfiddle

reference

Sorry I am not DBA. What is select 0, select 1 ... select 9 in view digits_v ? If I have more and 100 subjectId or classId .. does in mean I have to create 100 select in view digits_v? — fresher, Jan 01 '16 at 06:35
if you have more than 100 it means you need to add another cross join to digits. essentially all numbers are defined by the polynomial series an*10^n + ... a0*10^0 ( for unique values of an .. a0 ). this is one way to build a sequence for unrolling your variable length data. also if you have more than 100, normalising your data will give you much better performance than find_in_set — amdixon, Jan 01 '16 at 06:37

MySQL Select query to fetch record base on list values

2 Answers2

Linked