Find minimum datetime while using FK in two different tables

Manoj Maharana

9y791

I have 2 tables:

COURSE
------
Id
Name
TEST
------
Id
CourseId (FK to `COURSE.ID`)
DATETIME
NUMBERS

Suppose COURSE table with ID 1,2 (only 2 columns) and TEST table with 8 numbers of data having different DATETIME and CourseId of 1 (3 columns) and 2 (6 columns).

I want to find the minimum DATETIME,CourseID and Name by joining these 2 tables. The below query is giving a 2 output:

(SELECT min([DATETIME]) as DATETIME ,[TEST].CourseID,Name
FROM [dbo].[TEST]
left JOIN [dbo].[COURSE]
ON [dbo].[TEST].CourseID=[COURSE].ID GROUP BY CourseID,Name)

I want single output i.e. a single output column (minimum datetime along with Name and ID)..HOW can i achieve??

With 2 courses i always get 2 rows when joining like this. It will give me the minimum date value for each course.

so any suggestion?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
In fact, your query looks good to me. What do you mean by single output? If you mean the course Id and name of lowest date in another table try this:

select c.id, c.name, md.datetime
from dbo.course c
left outer join (
   select courseId, datetime
   from test
   where datetime = (
      select min(datetime)
      from test
   )
) md on c.id = md.courseId
0
Nitin Sontakke9y ago
select c.id, c.name, minDate
from dbo.Course c
left outer join
(
select CourseId
min(DateTime) minDate
from dbo.Test
group by CourseId
) md on c.id = md.CourseId
0