CODEWITHSUNDEEP
mysqlsql
stressed out
stressed out

Reputation: 552

Subtract the values of one column for records coming from two different selections that are not necessarily the same

Here is a simple statement of the problem:

Assume that we have issued a SELECT query that has outputted something like this:

student_id |  course_id  | status_id | created_at
     1            1            1       some_date1
     2            1            1       some_date2
     6            2            1       some_date3

And another SELECT query on the same table that has outptted something like this:

student_id |  course_id  | status_id |     created_at
     1            1            3       some_other_date1
     2            1            2       some_other_date2
     4            3            2       some_other_date3

So, both tables are in fact temporary tables.

I want a SQL query (I'm using MySQL) that takes both selections, considers only what is in common between both selections based on student_id and course_id and outputs student_id, course_id and the time difference between created_at values for each record.

Any help is appreciated. Even a clue about some SQL capability that I don't know of is appreciated.

Upvotes: 0

Views: 26

Answers (1)

Akina
Akina

Reputation: 42728

SELECT student_id, course_id, DATEDIFF(t1.created_at, t2.created_at) difference
FROM (
       SELECT student_id, course_id, /* status_id, */ created_at
       FROM /* the rest of query 1 */ 
     ) AS t1
JOIN (
       SELECT student_id, course_id, /* status_id, */ created_at
       FROM /* the rest of query 2 */ 
     ) AS t2 USING (student_id, course_id)

Upvotes: 1

Related Questions