how to display two details of tables from a correlated subquery. #mysql

If I am following correctly, you can use window functions:

select p.*, a.*
from package p
inner join (
    select a.*, rank() over(partition by activityid order by activitycost) rn
    from activity a
) a on p.PackageActivityID = a.activityid
where a.rn = 1

Basically, this brings the less details of the less expensive activity for each package – which is how I understand your question. Ties are allowed here.

In MySQL < 8.0, where window functions are not available, an alternative uses a correlated subquery for filtering:

select p.*, a.*
from package p
inner join activity a  on p.PackageActivityID = a.activityid
where a.activitycost = (
    select min(a1.activitycost)
    from activity a1
    where a1.activityid = a.activityid
)