CODEWITHSUNDEEP
sqloracle-database
Grant
Grant

Reputation: 2014

Return distinct matching and non-matching rows (outer join?)

I know I've done this in the past and I can't think of the solution now... I know it's some sort of full outer join.

I've got 3 tables:

ACTIVITIES       
id   name        
---+-------------
1  |  Basketball 
2  |  Chess Club 

ENROLLMENT                   
id   activity_id   person_id 
---+-------------+-----------
1  | 1           | 1         
2  | 1           | 2         
3  | 2           | 1         

ATTENDANCE
id   date         person_id   activity_id
---+-------------+----------+---------------
1  | 2017-01-01  | 1        | 1
2  | 2017-01-01  | 2        | 1
3  | 2017-01-02  | 1        | 1

I'm trying to get attendance by person_id, even when that id doesn't exist for a date:

date          person_id
------------+---------------
2017-01-01  | 1
2017-01-02  | null

Here's something close to what I think I'll need...

select date, attendance.person_id
from enrollment
**SOME SORT OF JOIN** attendance on enrollment.person_id = attendance.person_id
where person_id = 1

But all I can get returned is:

date          person_id
------------+---------------
2017-01-01  | 1
2017-01-01  | 1

...where the number of rows is correct but the values are wrong.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

This would seem to result in the results you want:

select date,
       max(case when person_id = 1 then person_id end) as person_id
from attendance a
group by date
order by date;

Upvotes: 1

Related Questions