0

I would like to perform select on joined tables and retrieve those rows, where at least one of four column is between two given dates.

Currently I have this:

SELECT
    t1.Id,
    t1.DateCr AS t1DateCr,
    t1.DateUpd AS t1DateUpd,
    t2.DateCr AS t2.DateCr,
    t2.DateUpd AS t2DateUpd
FROM
    Table1 AS t1,
    Table2 AS t2,
WHERE
    t1.Id = t2.t1Id
  AND (t1.DateCr BETWEEN '2018-10-29' AND '2018-11-04'
     OR t1.DateUpd BETWEEN '2018-10-29' AND '2018-11-04'
     OR t2.DateCr BETWEEN '2018-10-29' AND '2018-11-04'
     OR t2.DateUpd BETWEEN '2018-10-29' AND '2018-11-04')
GROUP BY t1.Id

Can this be written in more elegant way? without repeating the BETWEEN condition several times?

I can not use GREATEST and LEAST, because I dont want to select rows, where dates are bigger and smaller than given range, but not inside it.

And (t1.DateCr,t1.DateUpd,t2.DateCr,t2.DateUpd) BETWEEN '2018-10-29' AND '2018-11-04' does not work.

Zbynek
  • 5,673
  • 6
  • 30
  • 52
  • 2
    Define more elegant. – Salman A Nov 05 '18 at 10:20
  • Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 05 '18 at 10:29
  • You can use a query that uses `DISTINCT` and `UNION ALL` and you only type the two dates once. But it would only result in a messier query. – Salman A Nov 05 '18 at 12:11
  • Any time you see a GROUP BY clause absent of any aggregating functions, you can be sure that something's gone very wrong somewhere. Equally, when you see a suggested answer that reiterates the error, the safest course of action is simply to ignore it. – Strawberry Nov 05 '18 at 12:32
  • See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Nov 05 '18 at 12:34

0 Answers0