By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `tblqtdtl` (`DocNo` VARCHAR(255), `ItCode` VARCHAR(255), `Price` DECIMAL(7,2));
INSERT INTO `tblqtdtl` (`DocNo`, `ItCode`, `Price`) VALUES
('QT001', 'It004', 38700.00),
('QT002', 'It002', 25600.00),
('QT003', 'It005', 21000.00),
('QT004', 'It004', 34000.00),
('QT005', 'It003', 32000.00),
('QT006', 'It002', 31780.00),
('QT007', 'It001', 28000.00),
('QT008', 'It002', 21800.00),
('QT009', 'It004', 32600.00),
('QT010', 'It002', 27500.00);
CREATE TABLE `tblitem` (`ItCode` VARCHAR(255), `ItName` VARCHAR(255), `ItCtCode` VARCHAR(255));
INSERT INTO `tblitem` (`ItCode`, `ItName`, `ItCtCode`) VALUES
('Itestt001', 'Cotton', 'ALA'),
('It002', 'Polyester', 'SIN'),
('It003', 'Wool', 'ALA'),
('It004', 'Silk', 'ALA'),
('It005', 'Rayon', 'SIN');
CREATE TABLE `tblqthdr` (`DocNo` VARCHAR(255), `DocDt` VARCHAR(255), `VdCode` VARCHAR(255));
INSERT INTO `tblqthdr` (`DocNo`, `DocDt`, `VdCode`) VALUES
('QT001', '20180102', 'Vd003'),
('QT002', '20180203', 'Vd001'),
('QT003', '20180203', 'Vd002'),
('QT004', '20180207', 'Vd004'),
('QT005', '20180304', 'Vd003'),
('QT006', '20180401', 'Vd003'),
('QT007', '20180701', 'Vd005'),
('QT008', '20180902', 'Vd002'),
('QT009', '20180902', 'Vd005'),
('QT010', '20181203', 'Vd004');
CREATE TABLE `tblvendor` (`VdCode` VARCHAR(255), `VdName` VARCHAR(255), `ActInd` VARCHAR(255));
INSERT INTO `tblvendor` (`VdCode`, `VdName`, `ActInd`) VALUES
SELECT DISTINCT
VdCode,
VdName,
FIRST_VALUE(ItName) OVER (PARTITION BY VdCode ORDER BY Price ASC) `Cheapest item`,
MIN(Price) OVER (PARTITION BY VdCode) `Cheapest price`,
FIRST_VALUE(ItName) OVER (PARTITION BY VdCode ORDER BY Price DESC) `Most expensive item`,
MAX(Price) OVER (PARTITION BY VdCode) `Most Expensive Price`
FROM ( SELECT *
FROM tblqthdr C
INNER JOIN tblvendor V USING (VdCode)
INNER JOIN tblqtdtl B USING (DocNo)
INNER JOIN tblitem A USING (ItCode)
WHERE V.ActInd='Y' ) subquery
| VdCode | VdName | Cheapest item | Cheapest price | Most expensive item | Most Expensive Price |
|---|---|---|---|---|---|
| Vd001 | ABC Textile | Polyester | 25600.00 | Polyester | 25600.00 |
| Vd003 | GHI Textile | Polyester | 31780.00 | Silk | 38700.00 |
| Vd004 | JKL Textile | Polyester | 27500.00 | Silk | 34000.00 |