How to only return a calculated value to results after a SQL join?

Question

I have two tables and I only want a start date from a query on table2 to be added to the values on table1. The start date is deduced with a GROUP BY statement, for example:

Table 1

ID	Summary	End
1	Heading	01/01/2023
2	Heading 2	02/02/2023

Table 2

ID	Summary	Start	Parent ID
3	Heading	01/10/2022	1
4	Heading 2	02/10/2023	1
5	Heading 3	03/10/2023	2

I would like to have all of table 1 with just the minimum start date for each Parent ID from Table 2.

I tried something like this but I can't get the extra columns to return, I'm guessing I need to change something with the GROUP BY clause?

SELECT table1.*, t2.Start
FROM table1 INNER JOIN 
(
    SELECT [Parent ID], min(Start)
    FROM table2
    GROUP BY [Parent ID]
) t2 ON t2.[Parent ID] = t1.ID

Answer 1

;with a as(
Select row_number() over (PARTITION BY Table1.id order by table2.start) as 'row',table1.*,table2.start)
from table1 left join table2 on table1.id=table2.id)
select * from a where row=1

Answer 2

SELECT t1.*, t2.Start
FROM table1 t1 inner join 
    (
        select [Parent ID], min(Start) as Start
        from table2
        group by [Parent ID]
    ) t2 on t2.[Parent ID] = t1.ID

Answer 3

An other way to do it is by using GROUP BY and the aggregation function min() :

select t1.ID, max(t1.Summary) as Summary, max(t1.End) as End, min(t2.Start_) as min_start
from table1 t1
inner join table2 t2 on t1.id = t2.Parent_ID
group by t1.ID

Answer 4

An alternative solution rather than using a GROUP BY clause is to fetch the result within a subquery instead.

SELECT t1.*, (
    SELECT min(Start)
    FROM table2 t2
    WHERE t2.ParentID = t1.ID
) 
FROM table1 t1;

Answer 5

You need to modify your subquery to include the Parent ID in the select list, so it's available to the outer query for the join. Example:

SELECT table1.*, t2.MinStart
FROM table1
INNER JOIN (
    SELECT [Parent ID], MIN(Start) as MinStart
    FROM table2
    GROUP BY [Parent ID]
) as t2 ON t2.[Parent ID] = table1.ID

This query retrieves all columns from table1 and the minimum start date from table2 for each corresponding Parent ID. The subquery is used to calculate the minimum start date for each Parent ID in table2, and this result is then joined to table1 on the ID column.