sql server - Find minimum datetime while using FK in two different tables

Question

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 a single column output i.e. a single output column (minimum datetime along with Name and ID)..HOW can i achieve??

Answer 1

With 2 courses you are always going to get 2 rows when joining like this. It will give you the minimum date value for each course. The first way you can get a single row is to use TOP 1 in your query, which will simply give you the course with the earliest test date. The other way is to use a WHERE clause to filter it by a single course.

Please run this sample code with some variations of what you can do, notes included in comments:

CREATE TABLE #course ( id INT, name NVARCHAR(20) );

CREATE TABLE #Test
    (
      id INT ,
      courseId INT ,
      testDate DATETIME -- you shouldn't use a keyword for a column name
    );

INSERT  INTO #course
        ( id, name )
VALUES  ( 1, 'Maths' ),
        ( 2, 'Science' );

-- note I used DATEADD(HOUR, -1, GETDATE()) to simply get some random datetime values
INSERT  INTO #Test
        ( id, courseId, testDate )
VALUES  ( 1, 1, DATEADD(HOUR, -1, GETDATE()) ),
        ( 2, 1, DATEADD(HOUR, -2, GETDATE()) ),
        ( 3, 1, DATEADD(HOUR, -3, GETDATE()) ),
        ( 4, 2, DATEADD(HOUR, -4, GETDATE()) ),
        ( 5, 2, DATEADD(HOUR, -5, GETDATE()) ),
        ( 6, 2, DATEADD(HOUR, -6, GETDATE()) ),
        ( 7, 2, DATEADD(HOUR, -7, GETDATE()) ),
        ( 8, 2, DATEADD(HOUR, -8, GETDATE()) );

-- returns minumum date for each course - 2 rows
SELECT  MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left join
        INNER JOIN #course c ON t.courseId = c.id
GROUP BY courseId , name;

-- to get course with minimum date - 1 row
SELECT TOP 1
        MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left join
        INNER JOIN #course c ON t.courseId = c.id
GROUP BY t.courseId , c.name
ORDER BY MIN(t.testDate); -- requires order by

-- to get minimum date for a specified course - 1 row
SELECT  MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left join
        INNER JOIN #course c ON t.courseId = c.id
WHERE t.courseId = 1 -- requires you specify a course id
GROUP BY courseId , name;

DROP TABLE #course;
DROP TABLE #Test;