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.
[StudentMaster]
with a view and use triggers to be used by the current application(s) and then bit by bit change the code until you no longer need the current situation. Not beautiful but you end up with a better database.