SQLite Query using nested SELECT

Question

I'm trying to select cpu_time from the db, but the time must correspond with a few other criteria - i.e. build_version, test_letter, design_index, multi_thread, and test_index

What I thought would work was (the inner SELECT DISTINCT statement works on its own):

set query [db eval \
    {SELECT DISTINCT cpu_time WHERE cpu_time IN 
            (SELECT DISTINCT mgc_version, test_type_letter, design_index, 
                             test_index, cpu_time, multi_thread 
                    FROM TestExecutions WHERE test_type_letter
                    BETWEEN $testletter AND $testletter)}]

***Note - this is giving me a "no such column: cpu_time" error

where my first SELECT would pull all items from a distinct return. Then, from each return, I wanted to ONLY use the cpu_time, for each type of $testletter.

This is for generating CSV files that only have the cpu_time. Is it obvious what I'm getting wrong?

Thank you!

Answer 1

The main direct problem is that the IN operator expects a list of values, rather than projection - i.e. a table of columns and rows. You could perhaps use this similar query:

SELECT DISTINCT cpu_time
FROM (
    SELECT DISTINCT
        mgc_version,
        test_type_letter,
        design_index,
        test_index,
        cpu_time,
        multi_thread
    FROM
        TestExecutions
    WHERE test_type_letter BETWEEN $testletter AND $testletter
) AS x

The use of x for the alias is entirely arbitrary. Your outer query is also missing a FROM clause, but that's a side affect of trying something that doesn't quite work.

Answer 2

You should always use WHERE xxx IN (SELECT xxx FROM ...), instead of selecting multiple items in the inner select. You can add those in the outer select though, for example:

SELECT DISTINCT 
    mgc_version, 
    test_type_letter, 
    design_index, 
    test_index, 
    cpu_time, 
    multi_thread 
FROM TestExecutions
WHERE cpu_time IN 
(
    SELECT DISTINCT cpu_time 
    FROM TestExecutions 
    WHERE test_type_letter BETWEEN $testletter AND $testletter
)