SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Order_tbl (
id int ,
total decimal(5,2));

insert into Order_tbl values 
(1,55.09),
(2,62.42);

create table OrderLine (
order_id int ,
line_number int,
item varchar(20),
qty int );

insert into OrderLine values
(1,1,'Product A',10),
(1,2,'Product B',15),
(1,1,'Product A',12),
(1,1,'Product A',13),
(2,1,'Product A',23),
(3,1,'Product A',10),
(3,2,'Product B',15);
9 rows affected

select * from Order_tbl;
select * from OrderLine;


id
total


1
55.09

2
62.42




order_id
line_number
item
qty


1
1
Product A
10

1
2
Product B
15

1
1
Product A
12

1
1
Product A
13

2
1
Product A
23

3
1
Product A
10

3
2
Product B
15


select o.id,o.total
from Order_tbl o 
inner join ( select order_id
             from OrderLine
             where item in ('Product A','Product B')
             group by order_id
             having count(distinct item)=2  and  sum(case when item = 'Product A' then qty end ) > 20
            ) as ol on o.id=ol.order_id;



id
total


1
55.09


Warning: Null value is eliminated by an aggregate or other SET operation.

order_id	line_number	item	qty
1	1	Product A	10
1	2	Product B	15
1	1	Product A	12
1	1	Product A	13
2	1	Product A	23
3	1	Product A	10
3	2	Product B	15