SQL WHERE IN ... to JOIN table

Question

SELECT
    sum(CheckFinal.SUM) AS SUME,
    strftime('%Y - %m', CheckDate) AS CheckDate
FROM 
    CheckFinal
WHERE CheckFinal.NUMER IN (
    SELECT 
        CheckDetail.NUMER
    FROM
        CheckDetail
    WHERE
        CheckDetail.NUMER IN (
            SELECT 
                PriceList.UniqID AS PriceListUniqID, 
                PriceList.Name AS PriceListName,
                Category.UniqID
            FROM
                PriceList Join Category on PriceList.CATEGORY = Category.UniqID
            WHERE (Category.UniqID = 2)
        )
)
GROUP BY strftime('%Y %m', CheckDate);

I have such query to combine data out of 4 tables: — Category (100 records) — PriceList (20'000 records) — CheckDetail (10'000'000 records) — CheckFinal (2'000'000 records)

In plain word, I'm looking for PriceList items, that are marked as children of Category.UniqID #2, then I would like to collect all CheckDetail.NUMER inset to define all sales value with such PriceList items. Futhermore, I'm looking for possobility to collect all CheckFinal.NUMERs.

The problem I have is:

1. It's not possible to make SELECT procedure three (3) time nested (SQLite.3), I think it's time to make JOINs but I have no experience in joining
2. CheckDetail is a HUGE data set, it's take 2 seconds to find just one PriceList item across 10 million records and I have 3'000 items in my query WHERE (Category.UniqID = 2)

In my case, I should lookup 3'000 times through 5'000'000 records, but I have 10 sets and the query will spend about 10 hours to complit.

Is JOIN will optimize query time? How to make such JOIN QUERY?

Is there any GUI tools to make query with constructor or something like that?

UPD:

http://sqlfiddle.com/#!5/50a93/2 (use SQL.js for inserting several rows of data)

Answer 1

WITH JOIN , you query would look like

       SELECT 
           sum(CF.SUM) AS SUME,
           strftime('%Y - %m', CF.CheckDate) AS CheckDate
       FROM
            PriceList 
            Join Category 
            on PriceList.CATEGORY = Category.UniqID
            AND Category.UniqID = 2
            JOIN CheckDetail CD
            ON CD.NUMBER = PriceList.UniqID
            JOIN CheckFinal CF
            ON CF.NUMBER = CD.NUMBER
        GROUP BY strftime('%Y - %m', CF.CheckDate);