Sql query without sub query and joins

Question

To have students (student_id, course_id, course_name) who take exactly only one course, using a simple SQL statement with no sub-query and no join at all

student_id  course_id   course_name
---------------------------------------------
1       1000        'CS 101'
2       1000        'CS 101'
1       2000        'CHEM 200'
2       3000        'ENG 211'
3       1000        'CS 101'
4       5000        'Bio 400'

Any one please me on this. Tried many approaches but couldn't get the answer without sub query.

Do you want to get students who have only 1 course associated? — Abhishek Ginani, Dec 15 '15 at 07:21
@jarlh - I guess this is homework: schools tend not bother with niceties like third normal form. — APC, Dec 15 '15 at 19:32

Abhishek Sharma · Answer 1 · 2015-12-15T07:50:31.750

1

Use group by with having

select student_id,count(course_id) as num 
from students group by student_id
having num =1

or this :-

select student_id
from students group by student_id
having count(course_id) =1

edited Dec 15 '15 at 07:50

answered Dec 15 '15 at 07:24

Abhishek Sharma

6,689
1
14
20

2

you can't use aliases in `having`. It should be `having count(course_id)=1` – Tatiana Dec 15 '15 at 07:33
1

please see this link http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql @Tatiana – Abhishek Sharma Dec 15 '15 at 07:37
1

it is valid for MySQL, not for Oracle. And I see tag Oracle here @rahautos – Tatiana Dec 15 '15 at 07:45
1

And he sees the `mysql` tag here too :P – Drew Dec 15 '15 at 07:48
1

As far as I know `having count(course_id)=1` will work for both DB – Tatiana Dec 15 '15 at 07:51

score 1 · Answer 2 · answered Dec 15 '15 at 07:38

It is possible to retrieve just the STUDENT_ID for students on just one course:

SQL>  SELECT student_id
  2   FROM students 
  3   GROUP BY student_id
  4   HAVING COUNT(course_id) = 1 
  5   /


STUDENT_ID
----------
         4
         3

Elapsed: 00:00:00.09
SQL>

But to get additional information, such as the course name, then we need to use a sub-query or join.

japzdivino · Answer 3 · 2015-12-15T08:17:34.420

-2

How about using GROUP BY and HAVING COUNT

SELECT student_id, course_name FROM students 
GROUP BY student_id, course_name
HAVING COUNT(course_id) = 1

edited Dec 15 '15 at 08:17

answered Dec 15 '15 at 07:25

japzdivino

1,736
3
17
25

1

Having use to filter group data. you should place it just after the group by. – Abhishek Ginani Dec 15 '15 at 07:33
This query doesn't return the results you think it does. – APC Dec 15 '15 at 07:40
Sorry my mistake , `GROUP BY` first before the `HAVING COUNT` , answer already modified... my apologies. – japzdivino Dec 15 '15 at 08:18

Sql query without sub query and joins

3 Answers3