java - Selecting parent records of children that meet multiple conditions (but not together) with SQLite

Question

I don't know why I can wrap my head around this query. I think I am not asking the correct question which is why I am struggling to find an answer.

I have two tables. items and skus. I am trying to identify items that have skus that meet both the following conditions:

1. Has a SKU with a price_date > specified date
2. Has a SKU with a custom = 1.

Using the below example:

items

ID	Name
1	My Item 1
2	My Item 2

question from:https://stackoverflow.com/questions/65621425/selecting-parent-records-of-children-that-meet-multiple-conditions-but-not-toge

Answer 1

Instead of JOIN skus, we need to first figure out which ItemID-s qualify.. I am using MCNT as table prefix Multiple Conditions Not Together...

SELECT [ItemID]
  FROM [StackOver].[dbo].[MCNTskus]
  Group By [ItemID]
  Having 
       max([price_date]) > ('2020-12-01')
     and 
       max([custom])  = 1

Then use that as FROM and do LeftJoin of items

With qualSKUs as
(SELECT [ItemID]
  FROM [StackOver].[dbo].[MCNTskus]
  Group By [ItemID]
  Having 
       max([price_date]) > ('2020-12-01')
     and 
       max([custom])  = 1
)
SELECT MCNTitems.ID, MCNTitems.Name
    FROM qualSKUs
LEFT JOIN MCNTitems ON MCNTitems.ID=qualSKUs.ItemID
GROUP BY qualSKUs.ItemID,MCNTitems.ID, MCNTitems.Name

Resulting in

ID  Name
1   My Item 1