从你的样本数据和预期结果
你可以试着 UNION ALL 合并 tblpurchaseditems 以及 tblpurchaseditems table和牌子 grp 两个结果集。然后使用条件聚合函数来获得期望的结果。

CREATE TABLE tblpurchaseditems(
   ID INT,
   fruit_id INT,
   qty INT,
   amount INT
);

INSERT INTO tblpurchaseditems VALUES (1,1,3,75);
INSERT INTO tblpurchaseditems VALUES (2,1,2,50);
INSERT INTO tblpurchaseditems VALUES (3,2,1,10);
INSERT INTO tblpurchaseditems VALUES (4,3,3,30);

CREATE TABLE tblsolditems(
   ID INT,
   fruit_id INT,
   qty INT,
   amount INT
);

INSERT INTO tblsolditems VALUES (1,1,2,150);
INSERT INTO tblsolditems VALUES (2,1,2,350);
INSERT INTO tblsolditems VALUES (3,2,1,50);
INSERT INTO tblsolditems VALUES (4,3,2,230);
INSERT INTO tblsolditems VALUES (5,3,1,120);

查询1：

SELECT (@RN:=@RN+1) ID,
       fruit_id, 
       SUM(CASE WHEN grp = 1 THEN  qty END) totalqtypurchased ,
       SUM(CASE WHEN grp = 1 THEN  amount END) totalamountpurchased  ,
       SUM(CASE WHEN grp = 2 THEN  qty END) totalqtysold,
       SUM(CASE WHEN grp = 2 THEN  amount END) totalamountsold
FROM (
  select 1 grp,fruit_id,qty,amount from tblpurchaseditems
  UNION ALL 
  SELECT 2,fruit_id,qty,amount FROM tblpurchaseditems
)t1 CROSS JOIN (SELECT @RN:=0) v
GROUP BY fruit_id

结果：

| ID | fruit_id | totalqtypurchased | totalamountpurchased | totalqtysold | totalamountsold |
|----|----------|-------------------|----------------------|--------------|-----------------|
|  1 |        1 |                 5 |                  125 |            5 |             125 |
|  2 |        2 |                 1 |                   10 |            1 |              10 |
|  3 |        3 |                 3 |                   30 |            3 |              30 |

笔记
我会用一根柱子 itemGroup 在表中，可以表示哪种类型。 1 中庸
purchaseditems 2 中庸 solditems 不需要使用 UNION ALL 把两张table合起来，更有意义。
所以在表中，模式看起来像。

CREATE TABLE tblitems(
       ID INT,
       fruit_id INT,
       qty INT,
       amount INT,
       itemGroup INT
);

INSERT INTO tblitems VALUES (1,1,3,75,1);
INSERT INTO tblitems VALUES (2,1,2,50,1);
INSERT INTO tblitems VALUES (3,2,1,10,1);
INSERT INTO tblitems VALUES (4,3,3,30,1);

INSERT INTO tblitems VALUES (1,1,2,150,2);
INSERT INTO tblitems VALUES (2,1,2,350,2);
INSERT INTO tblitems VALUES (3,2,1,50 ,2);
INSERT INTO tblitems VALUES (4,3,2,230,2);
INSERT INTO tblitems VALUES (5,3,1,120,2);

查询1：

SELECT (@RN:=@RN+1) ID,
       fruit_id, 
       SUM(CASE WHEN  itemGroup = 1 THEN  qty END) totalqtypurchased ,
       SUM(CASE WHEN  itemGroup = 1 THEN  amount END) totalamountpurchased  ,
       SUM(CASE WHEN  itemGroup = 2 THEN  qty END) totalqtysold,
       SUM(CASE WHEN  itemGroup = 2 THEN  amount END) totalamountsold
FROM tblitems t1 CROSS JOIN (SELECT @RN:=0) v
GROUP BY fruit_id

结果：

| ID | fruit_id | totalqtypurchased | totalamountpurchased | totalqtysold | totalamountsold |
|----|----------|-------------------|----------------------|--------------|-----------------|
|  1 |        1 |                 5 |                  125 |            4 |             500 |
|  2 |        2 |                 1 |                   10 |            1 |              50 |
|  3 |        3 |                 3 |                   30 |            3 |             350 |

select a.fruit_id,sum(b.qty),sum(b.amount),sum(c.qty),sum(c.amount) 
from tblfruit a,tblpurchaseditems b,tblsolditems c 
where a.fruit_id=b.fruit_id and a.fruit_id=c.fruit_id
group by a.fruit_id

所以有两种方法可以做到这一点，在线或使用连接。
inline：选择name，（从tableb中选择sum（qty）作为tablea中的totalqty
join：选择a.name，sum（b.qty）作为totalqty from tablea join tableb on b.itemid=a.itemid按a.name分组
对于多个表，由于重复，使用连接会变得更加困难，因此对于较小的查询，内联查询在这里可以更好地工作。