Select with min max from another table-CodePudding

I have 2 tables.

Table1 has total of 20 columns, all related to training classes delivered or to be delivered. Columns include:

ClassID
ClassName
ClassStatus
Country
Requestor

Each row is unique, meaning ClassID is not repeated.

Table 2 shows the start datetime and end datetime of classes:

ClassID
ClassStartDatetime
ClassEndDatetime

However, if a class ran for 3 days, it will have 3 rows, each row represent each day with corresponding start time and end time.

I would like to query for all the columns in Table 1, plus add StartDate and EndDate, where ClassID is the unique identifier.

When I select all from Table 1, min of StartDatetime, max of EndDatetime using INNER JOIN both tables, I cannot get unique row of ClassID.

I tried to start with select distinct, which also doesn't work.

Should I not use INNER JOIN?

CodePudding user response：

You can't get unique values in your final table because of the GROUP BY clause, which aggregates on all other fields. In order to solve your problem you could select minimum and maximum of StartDate and EndDate respectively before the join, as done followingly:

SELECT * 
FROM       Table1 
INNER JOIN (SELECT ClassID,
                   MIN(ClassStartDatetime),
                   MAX(ClassEndDatetime)
            FROM Table2
            GROUP BY ClassID) unique_classids
        ON Table1.ClassID = unique_classids.ClassID

This code is untested as long as the post does not include sample rows of the tables involved.

CodePudding user response：

You need to group by Table2 before to join it to Table1

SELECT T1.*, minClassStartDatetime, maxClassEndDatetime
FROM Table1 T1
JOIN (
        SELECT 
            ClassID, 
            MIN(ClassStartDatetime) minClassStartDatetime, 
            MAX(ClassEndDatetime) maxClassEndDatetime
        FROM Table2
        GROUP BY ClassID
) AS T2 ON T1.ClassID = T2.ClassID