CODEWITHSUNDEEP
mysqlsqldatabase
U.A.Malik
U.A.Malik

Reputation: 78

MySQL Join with min and max date

I have two tables

profile
--------
id
name

Profile_dates
-------------- 
id
profile_id
entry_date

The tables have 1 to many relationships

I want to get min and max entry_date for each profile like

id
name
profile_dates.first_date
profile_dates.last_date

the profiles_dates table can have thousands of entries per profile. The simple join was very expensive for me. Is there another option?

Upvotes: 1

Views: 649

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

Sometimes subquery performance is far better than left join. Please try this simple solution.

id, name, (select min(entry_date) from  Profile_dates pd where pd.profile_id=p.id) first_date,
(select max(entry_date) from  Profile_dates pd where pd.profile_id=p.id) last_date
from profile p

Upvotes: 1

eshirvana
eshirvana

Reputation: 24593

simply join two tables and group by id:

select p.id , name , min(pd.entry_date) first_date, max(pd.entry_date) last_date
from profiles p 
left join Profile_dates pd
 on p.id = pd.id
group by p.id , name

Upvotes: 1

Related Questions