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

Question

I want to display details of package along with the details of its least expensive subactivities. Suppose there is A01,A02,A03 A01 is the package and A03 is the least expensive childactivity, So my select statement should display details of A01,A03

However I do not understand how can I display the details of the least expensive sub-activity[A03] along with A01.

I know I have to use correlated subquery but still can't get it to work.

Right now it only displays result of package with least expensive.

DDLs

CREATE TABLE IF NOT EXISTS `Activity` (
  `ActivityID` VARCHAR(45) NOT NULL,
  `ActivityName` VARCHAR(45) NULL,
  `ActivityCost` float(2) NULL,
  `ActivityType` VARCHAR(45) NULL);

CREATE TABLE IF NOT EXISTS `Package` (
  `PackageActivityID` VARCHAR(45) NOT NULL,
  `ChildActivityID` VARCHAR(45) NOT NULL,
  INDEX `fk_Package_Activity1_idx` (`PackageActivityID` ASC) VISIBLE,
  INDEX `fk_Package_Activity2_idx` (`ChildActivityID` ASC) VISIBLE,
  CONSTRAINT `fk_Package_Activity1`
    FOREIGN KEY (`PackageActivityID`)
    REFERENCES `mydb`.`Activity` (`ActivityID`)
  CONSTRAINT `fk_Package_Activity2`
    FOREIGN KEY (`ChildActivityID`)
    REFERENCES `mydb`.`Activity` (`ActivityID`);

insert into activity values ('A01', 'Kayaking', 120, 'B');
insert into activity values ('B01', 'Seaking', 420, 'I');
insert into activity values ('C01', 'maya', 220, 'O');
insert into activity values ('D01', 'bing', 170, 'B');

insert into package (packageactivityid, childactivityid) values ('A01','C01');
insert into package (packageactivityid, childactivityid) values ('F01','F01');
insert into package (packageactivityid, childactivityid) values ('D01','D01');
insert into package (packageactivityid, childactivityid) values ('E01','E01');
insert into package (packageactivityid, childactivityid) values ('B01','B01');

To recap, I'm hoping to achieve this result:

+-------------------+-----------------+------------+--------------+--------------+--------------+
| PackageActivityID | ChildActivityID | ActivityID | ActivityName | ActivityCost | ActivityType |
+-------------------+-----------------+------------+--------------+--------------+--------------+
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| A01               | C01             | A01        | Kayaking     |          120 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| D01               | D01             | D01        | bing         |          170 | B            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
| B01               | B01             | B01        | Seaking      |          420 | I            |
+-------------------+-----------------+------------+--------------+--------------+--------------+

My attempted query:

select activityid, activityname, activitycost 
from package p, activity a
where p.PackageActivityID = a.ActivityID
and p.ChildActivityID = (select ActivityID
from package p,activity a
where a.activityid = p.ChildActivityID
having min(activitycost));

My query only displays the packageactivity details but not the details of childactivity.

Answer 1

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
)