CODEWITHSUNDEEP
sqlalasql
Tim Wheeler
Tim Wheeler

Reputation: 11

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

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

Upvotes: 1

Views: 105

Answers (5)

mathiasrw
mathiasrw

Reputation: 618

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.

Upvotes: 1

toha
toha

Reputation: 5510

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;

Upvotes: 1

MAHDI Saghafi Zadeh
MAHDI Saghafi Zadeh

Reputation: 21

;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

Upvotes: 1

SelVazi
SelVazi

Reputation: 16043

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

Upvotes: 2

Rams
Rams

Reputation: 2169

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

Upvotes: 2

Related Questions