Manoj Maharana

Manoj Maharana

  • NA
  • 362
  • 126.7k

Find minimum datetime while using FK in two different tables

Jan 11 2017 4:29 AM
I have 2 tables:
  1. COURSE  
  2. ------  
  3. Id  
  4. Name  
  5.   
  6. TEST  
  7. ------  
  8. Id  
  9. CourseId (FK to `COURSE.ID`)  
  10. DATETIME  
  11. 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:

  1. (SELECT  min([DATETIME]) as DATETIME ,[TEST].CourseID,Name  
  2.     FROM [dbo].[TEST]  
  3.     left JOIN [dbo].[COURSE]  
  4.     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? 

Answers (2)