I have a table [CourseMaster]
LIKE
CourseId CourseName
-----------------------
01 ABC
02 DEF
03 GHI
04 JKL
05 MNO
06 PQR
07 STU
And I have another table [StudentMaster]
for Student Details LIKE
ROLLNO NAME ADDRESS Course
------------------------------------------------
12345 RAM RAM ADDRESS 01,02,06
25695 HARI HARI ADDRESS 02,06
89685 JEFF JEFF ADDRESS 03,05,06,07
47896 DAISY DAISY ADDRESS 03
Here I want to fetch the Student details with CourseName
(Not CourseId
).
If the values in Course
is not comma separated than it would be very simple query
to fetch the details with join.
As of my knowledge I can run two queries
for the same result what I want, One query for fetching the details of student from [StudentMaster]
to the front end. And other one for only fetching the CourseName
from [CourseMaster]
by corresponding CourseId
through a loop.
But the fact I want the result by only one query
rather than write two queries
for this small task.
I guess it is 100% possible. And my expected result will look like:
ROLLNO NAME ADDRESS Course
-------------------------------------------
12345 RAM RAM ADDRESS ABC,DEF,PQR
25695 HARI HARI ADDRESS DEF,PQR
89685 JEFF JEFF ADDRESS GHI,MNO,PQR,STU
47896 DAISY DAISY ADDRESS GHI
Thank you and any valuable suggestion will be highly appreciate.
Best Answer
You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to change your schema, but so is constantly dealing with the limitations of leaving it like it is.
Anyway, with that said, you need a split function. Since your comma-separated values are numeric, you can get away with a variation on my XML function; there are several others to choose from in this blog post.
Now, your query is:
Again, this is a complicated solution, and because of your inferior database structure, the next query you have to perform will be equally convoluted and cumbersome. There's a reason this type of design is argued against in just about every blog, essay or book about the topic...