CODEWITHSUNDEEP
sqlruby-on-railspostgresqljoinactiverecord
chris P
chris P

Reputation: 6589

Can you alter the column value in a where clause during a join?

Let's say we are joining 5 tables, A/B/C/D/E on one column each. A -> B, B -> C, C -> D, D- > E.

Let's say we have 2 columns A.date and E.date.

We want joins only to occur where E.date is between A.date and A.date + 1.day.

Is there anyway to do this without adding a column to A for A.date + 1.day?

essentially something like .where("A.date < E.date AND (A.date + 1.day) > E.date")

Upvotes: 1

Views: 52

Answers (2)

Drag0nKn1ght
Drag0nKn1ght

Reputation: 260

You will be checking with only A.Date and A.Date+1, correct? So you can try this... You don't actually need to use BETWEEN

[your code]
WHERE
   (A.Date=E.Date OR
   DATEADD(day,1,A.Date)=E.Date)

Upvotes: 1

Burkhard
Burkhard

Reputation: 14738

Yes.

SELECT [...] FROM [...] WHERE A.date <= E.date AND E.date <= A.date + intervall '1day';

Just replace the [...] with the appropriate code. You should be able to use a BETWEEN, but it will be basically the same.

Upvotes: 1

Related Questions