CODEWITHSUNDEEP
sqlite
user234207
user234207

Reputation:

SQLite Query using nested SELECT

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!

Upvotes: 15

Views: 43645

Answers (1)

Sander Rijken
Sander Rijken

Reputation: 21615

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
)

Upvotes: 29

Related Questions