Sql query to Select data for same date

Question

I have a table that contains assignment_id, student_id, assignment_name and assignment_date. I have to write a SQL query to get list of students who submitted 2 assignments on same date. I also need assignment_name and assignment_date in output. I am stuck with which query can I write to get this data. Any help would be highly appreciated

Image of the data

(1) You should show your attempted query. (2) You should tag with the database you are using. — Gordon Linoff, Jul 23 '18 at 00:58
Hi Gordon, I am using Oracle SQL. I am very new to SQL queries and completely stuck with where to start from — user10119684, Jul 23 '18 at 00:59

score 0 · Answer 1 · edited Jul 23 '18 at 05:43

0

You can try something like this

In MYSQL Query

  SELECT student_id, training_date, GROUP_CONCAT(assignment_name)
     FROM
   table_name
   GROUP BY student_id, training_date HAVING COUNT(*) = 2

"Is there any function in oracle similar to group_concat in mysql?" might help you to replace GROUP_CONCAT in oracle SQL

edited Jul 23 '18 at 05:43

Manish Singh

934
1
12
27

answered Jul 23 '18 at 04:41

Hardik Nuwal

73
8

Hardik, thank you so much for your help. But GROUP_CONCAT is giving me an error. I think it doesnt work in Oracle – user10119684 Jul 23 '18 at 05:10
https://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-to-group-concat-in-mysql Hope this helps. – Hardik Nuwal Jul 23 '18 at 05:32

score 0 · Answer 2 · answered Jul 23 '18 at 17:45

You can use exists :

select t.*
from table t
where exists (select 1 
              from table t1 
              where t1.student_id = t.student_id and 
                    t1.training_date = t.training_date and 
                    t1.assignment_id <> t.assignment_id
             );

Sql query to Select data for same date

2 Answers2