Getting undesired output in SQL
up vote
0
down vote
favorite
I'm new beginner to SQL. I want to list all the codes and names of all subject. In the case of those subjects, for which any of the students registered for the first time, listing the recommended exam date(s) as well.
Order of columns of the result set:
neptuncode, subjectname, examdate
I ran the following query
SELECT
subjects.neptuncode AS "neptuncode",
subjects.subjectname AS "subjectname",
enrollments.examdate AS "examdate"
FROM
subjects
LEFT OUTER JOIN
enrollments ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate
HAVING
COUNT(enrollments.enrollmentdate) = 1
ORDER BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate;
The result is only subjects which registered by students for the first time alongside neptuncode and examdate. I would like to list also all subjects with their neptuncode and examdate (it should be NULL because only the subjects which are registered for the first time have exam date value in the column). But I am stuck at doing this. Any suggestions please?
sql
edited Nov 9 at 5:28
marc_s
566k12610921245
asked Nov 8 at 21:28
Emil Bədrəddinli
295
add a comment |
up vote
0
down vote
favorite
I'm new beginner to SQL. I want to list all the codes and names of all subject. In the case of those subjects, for which any of the students registered for the first time, listing the recommended exam date(s) as well.
Order of columns of the result set:
neptuncode, subjectname, examdate
I ran the following query
SELECT
subjects.neptuncode AS "neptuncode",
subjects.subjectname AS "subjectname",
enrollments.examdate AS "examdate"
FROM
subjects
LEFT OUTER JOIN
enrollments ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate
HAVING
COUNT(enrollments.enrollmentdate) = 1
ORDER BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate;
The result is only subjects which registered by students for the first time alongside neptuncode and examdate. I would like to list also all subjects with their neptuncode and examdate (it should be NULL because only the subjects which are registered for the first time have exam date value in the column). But I am stuck at doing this. Any suggestions please?
sql
edited Nov 9 at 5:28
marc_s
566k12610921245
asked Nov 8 at 21:28
Emil Bədrəddinli
295
2
Sample data and desired results would help. So would an appropriate database tag.
– Gordon Linoff
Nov 8 at 21:31
If you remove the HAVING clause, does that result look better?
– jarlh
Nov 8 at 21:35
When removing HAVING clause it gives all the exam dates with all subjects. I need exam dates with the subjects which is registered for the first time by any students
– Emil Bədrəddinli
Nov 8 at 21:37
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm new beginner to SQL. I want to list all the codes and names of all subject. In the case of those subjects, for which any of the students registered for the first time, listing the recommended exam date(s) as well.
Order of columns of the result set:
neptuncode, subjectname, examdate
I ran the following query
SELECT
subjects.neptuncode AS "neptuncode",
subjects.subjectname AS "subjectname",
enrollments.examdate AS "examdate"
FROM
subjects
LEFT OUTER JOIN
enrollments ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate
HAVING
COUNT(enrollments.enrollmentdate) = 1
ORDER BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate;
The result is only subjects which registered by students for the first time alongside neptuncode and examdate. I would like to list also all subjects with their neptuncode and examdate (it should be NULL because only the subjects which are registered for the first time have exam date value in the column). But I am stuck at doing this. Any suggestions please?
sql
edited Nov 9 at 5:28
marc_s
566k12610921245
asked Nov 8 at 21:28
Emil Bədrəddinli
295
I'm new beginner to SQL. I want to list all the codes and names of all subject. In the case of those subjects, for which any of the students registered for the first time, listing the recommended exam date(s) as well.
Order of columns of the result set:
neptuncode, subjectname, examdate
I ran the following query
SELECT
subjects.neptuncode AS "neptuncode",
subjects.subjectname AS "subjectname",
enrollments.examdate AS "examdate"
FROM
subjects
LEFT OUTER JOIN
enrollments ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate
HAVING
COUNT(enrollments.enrollmentdate) = 1
ORDER BY
subjects.neptuncode, subjects.subjectname, enrollments.examdate;
The result is only subjects which registered by students for the first time alongside neptuncode and examdate. I would like to list also all subjects with their neptuncode and examdate (it should be NULL because only the subjects which are registered for the first time have exam date value in the column). But I am stuck at doing this. Any suggestions please?
sql
sql
edited Nov 9 at 5:28
marc_s
566k12610921245
asked Nov 8 at 21:28
Emil Bədrəddinli
295
edited Nov 9 at 5:28
marc_s
566k12610921245
asked Nov 8 at 21:28
Emil Bədrəddinli
295
edited Nov 9 at 5:28
marc_s
566k12610921245
edited Nov 9 at 5:28
marc_s
566k12610921245
edited Nov 9 at 5:28
marc_s
566k12610921245
566k12610921245
asked Nov 8 at 21:28
Emil Bədrəddinli
295
asked Nov 8 at 21:28
Emil Bədrəddinli
295
asked Nov 8 at 21:28
Emil Bədrəddinli
295
295
2
Sample data and desired results would help. So would an appropriate database tag.
– Gordon Linoff
Nov 8 at 21:31
If you remove the HAVING clause, does that result look better?
– jarlh
Nov 8 at 21:35
When removing HAVING clause it gives all the exam dates with all subjects. I need exam dates with the subjects which is registered for the first time by any students
– Emil Bədrəddinli
Nov 8 at 21:37
add a comment |
2
Sample data and desired results would help. So would an appropriate database tag.
– Gordon Linoff
Nov 8 at 21:31
If you remove the HAVING clause, does that result look better?
– jarlh
Nov 8 at 21:35
When removing HAVING clause it gives all the exam dates with all subjects. I need exam dates with the subjects which is registered for the first time by any students
– Emil Bədrəddinli
Nov 8 at 21:37
2
2
Sample data and desired results would help. So would an appropriate database tag.
– Gordon Linoff
Nov 8 at 21:31
Sample data and desired results would help. So would an appropriate database tag.
– Gordon Linoff
Nov 8 at 21:31
If you remove the HAVING clause, does that result look better?
– jarlh
Nov 8 at 21:35
If you remove the HAVING clause, does that result look better?
– jarlh
Nov 8 at 21:35
When removing HAVING clause it gives all the exam dates with all subjects. I need exam dates with the subjects which is registered for the first time by any students
– Emil Bədrəddinli
Nov 8 at 21:37
When removing HAVING clause it gives all the exam dates with all subjects. I need exam dates with the subjects which is registered for the first time by any students
– Emil Bədrəddinli
Nov 8 at 21:37
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
Would you try this please
SELECT
subjects.neptuncode AS "neptuncode"
, subjects.subjectname AS "subjectname"
, CASE WHEN COUNT(enrollments.enrollmentdate) = 1 THEN MAX(enrollments.enrollmentdate)
ELSE NULL
END AS "examdate"
FROM subjects
LEFT OUTER JOIN enrollments
ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode
, subjects.subjectname
ORDER BY
subjects.neptuncode
, subjects.subjectname
;
I believe you need to treat the enrollmentdate as an aggregate, as only 1 enrollment date is permitted you can use either max() or min().
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In response to your latest description I have removed thehaving clauseand introduced acase expression. I probably will not attempt another query variant based on description of the result wanted.
– Used_By_Already
Nov 8 at 23:33
|
show 2 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Would you try this please
SELECT
subjects.neptuncode AS "neptuncode"
, subjects.subjectname AS "subjectname"
, CASE WHEN COUNT(enrollments.enrollmentdate) = 1 THEN MAX(enrollments.enrollmentdate)
ELSE NULL
END AS "examdate"
FROM subjects
LEFT OUTER JOIN enrollments
ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode
, subjects.subjectname
ORDER BY
subjects.neptuncode
, subjects.subjectname
;
I believe you need to treat the enrollmentdate as an aggregate, as only 1 enrollment date is permitted you can use either max() or min().
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In response to your latest description I have removed thehaving clauseand introduced acase expression. I probably will not attempt another query variant based on description of the result wanted.
– Used_By_Already
Nov 8 at 23:33
|
show 2 more comments
up vote
1
down vote
accepted
Would you try this please
SELECT
subjects.neptuncode AS "neptuncode"
, subjects.subjectname AS "subjectname"
, CASE WHEN COUNT(enrollments.enrollmentdate) = 1 THEN MAX(enrollments.enrollmentdate)
ELSE NULL
END AS "examdate"
FROM subjects
LEFT OUTER JOIN enrollments
ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode
, subjects.subjectname
ORDER BY
subjects.neptuncode
, subjects.subjectname
;
I believe you need to treat the enrollmentdate as an aggregate, as only 1 enrollment date is permitted you can use either max() or min().
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In response to your latest description I have removed thehaving clauseand introduced acase expression. I probably will not attempt another query variant based on description of the result wanted.
– Used_By_Already
Nov 8 at 23:33
|
show 2 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Would you try this please
SELECT
subjects.neptuncode AS "neptuncode"
, subjects.subjectname AS "subjectname"
, CASE WHEN COUNT(enrollments.enrollmentdate) = 1 THEN MAX(enrollments.enrollmentdate)
ELSE NULL
END AS "examdate"
FROM subjects
LEFT OUTER JOIN enrollments
ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode
, subjects.subjectname
ORDER BY
subjects.neptuncode
, subjects.subjectname
;
I believe you need to treat the enrollmentdate as an aggregate, as only 1 enrollment date is permitted you can use either max() or min().
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
Would you try this please
SELECT
subjects.neptuncode AS "neptuncode"
, subjects.subjectname AS "subjectname"
, CASE WHEN COUNT(enrollments.enrollmentdate) = 1 THEN MAX(enrollments.enrollmentdate)
ELSE NULL
END AS "examdate"
FROM subjects
LEFT OUTER JOIN enrollments
ON enrollments.subject = subjects.subject_ID
GROUP BY
subjects.neptuncode
, subjects.subjectname
ORDER BY
subjects.neptuncode
, subjects.subjectname
;
I believe you need to treat the enrollmentdate as an aggregate, as only 1 enrollment date is permitted you can use either max() or min().
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
edited Nov 9 at 1:41
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
answered Nov 8 at 21:58
Used_By_Already
21.6k21838
21.6k21838
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In response to your latest description I have removed thehaving clauseand introduced acase expression. I probably will not attempt another query variant based on description of the result wanted.
– Used_By_Already
Nov 8 at 23:33
|
show 2 more comments
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In response to your latest description I have removed thehaving clauseand introduced acase expression. I probably will not attempt another query variant based on description of the result wanted.
– Used_By_Already
Nov 8 at 23:33
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
It did not give the all subjects which is in the database
– Emil Bədrəddinli
Nov 8 at 22:15
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
Please re-try, I altered the query more than once (sorry). But it will not give ALL subjects - unless the count of enrollments is <= 1. If this query does not work then I suggest that trying to describe (in words) what you expect isn't working. Provide sample data and the expected result (as tables, not as words). Use fixed format text tables, not images, this url may help: ozh.github.io/ascii-tables
– Used_By_Already
Nov 8 at 22:52
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
I want to list all subject name and codes from database. In the case of those subjects for which any of the students registered for the first time, list exam date for this condition. In other words result is all subjects with subjectname, neptuncode and examdate. If above mentioned condition is true write the examdate in the colum otherwise leave as null for the rest which is not registered for the first time
– Emil Bədrəddinli
Nov 8 at 23:12
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In your mind you have an image of that expected result. The design of that result is well formed because you already know the data, you have seen it many times, plus you control the requirements as well. I know only the few words you have used; in 3 places (the question, and a comment under the question, and the comment just above) and in all those places there are different words/variations of that explanation. This is why words are not a great way to DEFINE the expected result - data is the best way.
– Used_By_Already
Nov 8 at 23:29
In response to your latest description I have removed the
having clause and introduced a case expression. I probably will not attempt another query variant based on description of the result wanted.– Used_By_Already
Nov 8 at 23:33
In response to your latest description I have removed the
having clause and introduced a case expression. I probably will not attempt another query variant based on description of the result wanted.– Used_By_Already
Nov 8 at 23:33
|
show 2 more comments
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53216409%2fgetting-undesired-output-in-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Sample data and desired results would help. So would an appropriate database tag.
– Gordon Linoff
Nov 8 at 21:31
If you remove the HAVING clause, does that result look better?
– jarlh
Nov 8 at 21:35
When removing HAVING clause it gives all the exam dates with all subjects. I need exam dates with the subjects which is registered for the first time by any students
– Emil Bədrəddinli
Nov 8 at 21:37