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?
CodePudding user response:
If what you want in the results is the DataSourceID
s 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.