find the list of student id . sql

Question

1.00/5 (3 votes)

See more:

write sql query for that.

find the list of student id who enrolled in one course english or Sindhi but not enrolled in both courses.

table name : course
having two columns
courseid|cname

table name : studentcourse
having two columns
studentid|courseid

Posted 19-Oct-14 7:17am

Member 10689154

Add a Solution

4 solutions

Solution 2

Try this Query..
i hope this will help you..

C#

select [StdId],count(distinct([CourseId]))  from [dbo].[tbl_Student_Course] group by [StdId]
 having count(distinct([CourseId]))=1

Posted 19-Oct-14 21:49pm

/\jmot

Updated 22-Oct-14 21:33pm

v2

Solution 4

Yet another solution without Count, Group By and Distinct:

Pretty verbose and I guess slower than the other ones because of its nestings:

C#

SELECT studentId
FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('English')
AND
studentId NOT IN
(SELECT studentId FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('Sindhi'))

UNION

SELECT studentId
FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('Sindhi')
AND
studentId NOT IN
(SELECT studentId FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('English'))

Posted 20-Oct-14 21:49pm

sja63

Solution 3

SQL

SELECT
	stdId
FROM
	studentcourse S
	JOIN course C ON S.courseid =C.courseid
WHERE
	C.cname IN ('english', 'Sindhi')
GROUP BY
	[sId]
HAVING
	COUNT(DISTINCT (C.courseid)) < 2

Posted 19-Oct-14 22:15pm

Arora_Ankit

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2014-10-19T07:53:00

We don't do homework. If you get stuck to something, people are ready to help in specific questions. However, you have to show that you have tried yourself and clearly point out what is the problem. Doing and trying yourself is the only way to learn and I bet that if you try, you'll succeed.

find the list of student id . sql

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

find the list of student id . sql

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us