Sql-server – Single value from a list of comma separated values when used in inner join in SQL Server

Use a subquery (as displayed) or CTE for that purpose:

SELECT *
FROM  (
   SELECT qid, gid
   FROM   table1
   ORDER  BY date DESC
   LIMIT  10
   OFFSET ?
   ) q
JOIN   table2 a USING (qid, gid)

USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.

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.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1) = ','
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'varchar(8000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Now, your query is:

;WITH x AS 
(
  SELECT s.ROLLNO, s.Name, s.Address, c.CourseId, c.CourseName
  FROM dbo.StudentMaster AS s
  CROSS APPLY dbo.SplitStrings_XML(s.Course, default) AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.item = c.CourseId
)
SELECT ROLLNO, Name, Address, STUFF((
  SELECT ',' + CourseName FROM x AS x2 
  WHERE x2.ROLLNO = x.ROLLNO
  ORDER BY CourseId FOR XML PATH, 
  TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
FROM x
GROUP BY ROLLNO, Name, Address;

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...