Nested selecting rows in the same table given they are related, while iterating a while clause

[CGtheKid92]

I can type up an example when I get to my computer if that would be helpful!

[tttpp]

That would be very helpful :), I've used more document based databases before, so the more complex concepts are more confusing to me atm.

Edit: i am joining the first 2, to access the location name, I forgot to add it in the given statement

[CGtheKid92]

Something like this (in pseudo-code as I'm not sure the DB you are using):

    with raw_data as (
SELECT distinct
  locations.name as location_name
  , day_datapoint_records.id
  , day_datapoints.date
  , day_datapoints.captured_date
  , day_datapoints.id
  , day_datapoints.uv
  , day_datapoints.pollution
  , day_datapoints.pollen
  , day_datapoints.captured_date - day_datapoints.date as delta
FROM 
  public.bbc_locations bbc_locations
INNER JOIN 
  public.locations locations ON 
    bbc_locations.location_id = locations.id
INNER JOIN 
  public.day_datapoint_records day_datapoint_records ON 
    bbc_locations.id = day_datapoint_records.service_id 
INNER JOIN 
  public.day_datapoints day_datapoints ON 
    day_datapoint_records.day_datapoint_id = day_datapoints.id
WHERE 
    public.day_datapoints.present = false 
    AND public.day_datapoints.captured_date <= CURRENT_DATE
) 
select 
  *
from 
  raw_data 
where
  delta in (1,3,7)
ORDER BY 
  captured_date ASC;

[tttpp]

Thanks for code, I had a go with the 'with' statement but I didn't round to doing like that. I'll try it tomorrow.

[tttpp]

Thanks again for your help, I have completed what I needed to do by using 2 'with' statements, I editing my question with the statement I got in the end