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

I should add that I currently only get the ParentID and Start value returned in the query. I want the ID, Summary, Start, End values — Tim Wheeler, May 11 '23 at 11:17
When you want to add something to your question, you should use [edit] — Luuk, May 11 '23 at 11:19

score 1 · Answer 1 · answered May 11 '23 at 11:38

1

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

answered May 11 '23 at 11:38

Rams

2,129
1
12
19

score 1 · Answer 2 · answered May 11 '23 at 12:27

1

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

answered May 11 '23 at 12:27

SelVazi

10,028
2
13
29

score 0 · Answer 3 · edited May 11 '23 at 20:19

0

;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

edited May 11 '23 at 20:19

GMB

216,147
25
84
135

answered May 11 '23 at 11:30

MAHDI Saghafi Zadeh

1
2

score 0 · Answer 4 · edited May 13 '23 at 18:12

0

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;

edited May 13 '23 at 18:12

Skully

2,882
3
20
31

answered May 11 '23 at 13:25

toha

5,095
4
40
52

1

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you edit your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Skully May 12 '23 at 15:36
1

This is a solution sharing platform where giving your answer is one part, but explaining it to allow future readers to understand what the solution is doing is equally as important. Whilst it is not a rule, it is highly encouraged to do so. – Skully May 13 '23 at 18:01

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

4 Answers4