1

I have two tables:

  • 1 with an Unique ID with the first time they visited (in date format)
  • and a historical addresses table with the same Unique ID, a start field (date field), an end field (date field) and the addresses fields.

What I like to do is create a table which get the addresses of the persons (Unique ID) based on the date where they lived at the time of the visit.

I'm able to select records with my own made up dates, but I would like to get them from the 'first time visit' field.

The SQL query with random dates:

SELECT * FROM historical_adresses
WHERE (startdate, enddate) OVERLAPS ('2013-05-15'::DATE, '2013-05-15'::DATE);

Who can help me do this in PostgresSQL?

2

If I understand your problem correctly, you have to join the other table to historical_adresses based on the unique ID and that the visit time falls between startdate and enddate.

If this is so, the query could look like:

SELECT [address_elements]
FROM historical_adresses AS ha
JOIN visits AS v 
    ON ha.user_id = v.user_id
    AND v.visit_time BETWEEN ha.startdate AND ha.enddate
WHERE [find the visit somehow that you are interested in];

Note: you usually don't need all columns of a table (and definitely not from a join), so use the actual column names rather than *.

  • Great!! That did the job. Quite logic now that I see the query, but still thanks! – McMave Nov 16 '15 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.