SQLite: Alternative Query for Nested SELECT Statements

user_4460247 0
Viewed 60

Here are the relevant columns for two tables:

DataSources
-----------
Column: DataSourceID --> Primary Key

TimeSteps
-----------
Column: TimeStepID   --> PrimaryKey
Column: Date         --> (of the form YYYY-MM-DD)
Column: DataSourceID --> Foreign Key

I could have the same Date for multiple datasources, but with a different TimeStepID. I would like to find which datasources share a date given only a single TimeStepID.

The query I came up with that works is as follows:

SELECT DISTINCT ds.DataSourceID FROM DataSources ds
JOIN TimeSteps AS ts USING(DataSourceID)
WHERE ts.TimeStepID IN
   (SELECT TimeStepID FROM TimeSteps WHERE Date IN
      (SELECT Date FROM TimeSteps WHERE TimeStepID = ?))

Is there a way to simplify this query without the nested SELECT statements?

subquery date sqlite sql database
1 Answers

If what you want in the results is the DataSourceIDs then there is no need for a join to DataSources.
Also, the query:

SELECT Date FROM TimeSteps WHERE TimeStepID = ?

returns only 1 row with 1 column, so there is no need for the operator IN.

Try this:

SELECT DISTINCT DataSourceID 
FROM TimeSteps
WHERE Date = (SELECT Date FROM TimeSteps WHERE TimeStepID = ?);

If the combination of DataSourceID and Date is unique, then DISTINCT is not needed.

user_104988280
Related