CODEWITHSUNDEEP
sqlpostgresqljoinwhere-clause
John
John

Reputation: 521

SQL select value from one column based on values in another column

I have two tables. One stores "locations":

TABLE location (
    ID               serial             PRIMARY KEY,
    name             text               NOT NULL,
    description      text               NOT NULL
);

Each location has many rows of "data":

TABLE data(
    ID               smallint           REFERENCES location(ID),
    date             date,               
    rainfall         int                
);

I would like to find all locations that have "data" spanning a given period. I have tried this:

SELECT location.ID, location.name FROM location                    
    JOIN data ON data.id = location.id 
    WHERE (SELECT MIN(data.date) FROM data) <= '$start_date' 
           AND 
          (SELECT MAX(data.date) FROM data) >= '$end_date' 
    ORDER BY location.ID;

but it seems to apply the MIN and MAX test to all the data, not to each individual location i.e. the test needs to be applied to every location, and only return those locations that pass the test.

Any suggestions?

Upvotes: 2

Views: 7932

Answers (5)

Dhiresh Jain
Dhiresh Jain

Reputation: 484

When you say spanning a given period, I suppose you mean output only those locations that have all their data.date between $start_date and $end_date.

In that case this query should work:

SELECT location.ID, location.NAME FROM locations, data
WHERE locations.ID=data.ID
GROUP BY locations.ID
HAVING min(data.date) >= '$start_date' and max(data.date) <= $end_date;

Upvotes: 0

Ram
Ram

Reputation: 1803

When you say spanning a given period, why not use between like below

SELECT location.ID, location.name FROM location location
JOIN data d ON d.id = location.id WHERE d.date between '$start_date' AND '$end_date' ORDER BY location.ID;

Note that both dates are inclusive here

Upvotes: 1

Yogs
Yogs

Reputation: 80

You could use between clause to get the desired results

SELECT l.ID, l.name FROM location l                   
    inner JOIN data d ON d.id = l.id 
    WHERE  d.date between '$start_date' and '$end_date' group by l.id

Upvotes: 0

Jameson the dog
Jameson the dog

Reputation: 1806

you could join a query result for those values and use that in the where condition

SELECT location.ID, location.name FROM location                    
JOIN data ON data.id = location.id 
JOIN (
    SELECT MIN(date) AS _min,MAX(date) AS _max,id
    FROM data
    GROUP BY id
) T ON T.id = location.id
WHERE T._min <= '$start_date' AND T._max >= '$end_date' 
ORDER BY location.ID;

just to clarify - I understood you want to get all the data for locations that have data in those time spans (the example implied this is what you wanted)

if you want to get all the data that "happened" in the time range this is not the solution for you, you want the between solution offered by @jusermar10 or @Yogs

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Just aggregate by location and then assert the min/max from the HAVING clause:

SELECT
    l.ID,
    l.name
FROM location l                 
INNER JOIN data d
    ON d.id = l.id
GROUP BY
    l.ID
HAVING
    MIN(data.date) <= '$start_date' AND
    MAX(data.date) >= '$end_date' 
ORDER BY
    l.ID;

Upvotes: 5

Related Questions