CODEWITHSUNDEEP
sqlsql-serveroracle-database
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33974

Parent Child Query without using SubQuery

Let say I have two tables,

Table A

ID  Name
--  ----
 1   A
 2   B

Table B

AID  Date
--  ----
 1   1/1/2000
 1   1/2/2000
 2   1/1/2005
 2   1/2/2005

Now I need this result without using sub query,

ID  Name  Date
--  ----  ----
 1   A    1/2/2000
 2   B    1/2/2005

I know how to do this using sub query but I want to avoid using sub query for some reason?

Upvotes: 2

Views: 397

Answers (3)

Max
Max

Reputation: 804

create table #t1 (id int, Name varchar(10))
create table #t2 (Aid int, Dt date)

insert #t1 values (1, 'A'), (2, 'B')
insert #t2 values (1, '1/1/2000'), (1, '1/2/2000'), (2, '1/1/2005'), (2, '1/2/2005')


;WITH cte (AId, MDt)
as
(
select Aid, MAX(Dt) from #t2 group by AiD
)
select #t1.Id, #t1.Name, cte.MDt
from #t1
join cte
    on cte.AId = #t1.Id

Upvotes: 1

JNK
JNK

Reputation: 65147

SELECT a.ID, a.Name, MAX(B.Date)
FROM TableA A
INNER JOIN TableB B
    ON B.ID = A.ID
GROUP BY A.id, A.name

It's a simple aggregation. Looks like you want the highest date per id/name combo.

Upvotes: 5

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

If I got your meaning right and you need the latest date from TableB, then the query below should do it:

select a.id,a.name,max(b.date)
from TableA a
join TableB b on b.aid = a.id
group by a.id,a.name

Upvotes: 6

Related Questions