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!

Try to use the code button (ones and zeroes) in the editor, or indent code by 4 spaces to get it looking right. — Sander Rijken
– Sander Rijken, Commented Dec 17, 2009 at 23:17

Sander Rijken · Accepted Answer · 2009-12-17 23:20:57Z

30

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
)

answered Dec 17, 2009 at 23:20

Sander Rijken

21.7k3 gold badges65 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitry Shevkoplyas Over a year ago

The "should always use WHERE xxx IN" statement is a bit strong and not justified. There are cases when nested selects should take other forms (subquery can also reside in SELECT and FROM parts of the query), which are best described by the following SQLite tutorial: techonthenet.com/sqlite/subqueries.php

Collectives™ on Stack Overflow

SQLite Query using nested SELECT

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related