CODEWITHSUNDEEP
sqlamazon-redshift
martina.physics
martina.physics

Reputation: 9804

SQL - selecting rows when field is between two columns of other table

Suppose I have two tables which contain, respectively:

| User        | birthday      | 
| ----------- |:-------------:|
| 'you'       | '1980-11-01'  | 
| 'me'        | '1986-12-27'  |

and

| Event        | date_start   |   date_end   | 
| ------------ |:-------------:|   ------------- | 
| 'e1'         | '1980-10-13'  |   '1980-12-01'
| 'e2'         | '1986-01-04'  |   '1987-01-01'
| 'e3'         | '2000-10-13'  |   '2003-12-01'

and suppose for each event in the second table I want to select all users whose birthday falls in between the timespan of their dates, meaning inside the interval between date_start and date_end.

Obviously a JOIN wouldn't suit this need, is there a way? For reference, I am particularly interested in doing this on a Redshift database.

Upvotes: 0

Views: 64

Answers (3)

Mark
Mark

Reputation: 418

Maybe this can be a solution:

SELECT
  ev.name,
  LISTAGG(user_name, ', ') WITHIN GROUP ( ORDER BY user_name DESC ) "Users"
FROM events ev
LEFT JOIN users u ON u.birthday >=  ev.datestart AND u.birthday <= ev.dateend
GROUP BY ev.name;

Upvotes: 0

Pelin
Pelin

Reputation: 966

You can use LISTAGG Function

select (select istagg(u.User) within group (order by u.User)   
        from user u 
        where 
        u.birthday >=  ev.date_start and u.birthday <  ev.date_end ) 
        from event_table ev

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

Why join is not sufficient?

select *
from event e
join user u on e.date_start < u.birthday and e.date_end > u.birthday

Upvotes: 2

Related Questions