CODEWITHSUNDEEP
sqlsql-server
Dan Walters
Dan Walters

Reputation: 1376

Select multiple rows or null row if no match exists

I have three tables. People, Activity and Activity Link

I'm trying to list all of the People and their allocation for Activity "a3" within a given date. Multiple allocations should be listed. People with no allocation should be listed as Null but the activity should be listed.

Conditions:

-Date: 04/10/2019 must be between start and end.

-Where people_id != p4

-SQL Server or equiv trans-sql, mysql etc.

People Table:

+-----------+
| people_id |
+-----------+
| p1        |
| p2        |
| p3        |
| p4        |
| p5        |
+-----------+

Activity Table:

+------------------+-----------+
| activity_type_id |   name    |
+------------------+-----------+
| a1               | monday    |
| a2               | tuesday   |
| a3               | wednesday |
| a4               | thursday  |
| a5               | friday    |
+------------------+-----------+

Activity Link Table:

+-------------------------+--------+----------+------------+------------+
| activity_people_link_id | people | activity |   start    |    end     |
+-------------------------+--------+----------+------------+------------+
|                       1 | p1     | a1       | 03/10/2019 | 10/10/2019 |
|                       2 | p1     | a3       | 02/10/2019 | 10/10/2019 |
|                       3 | p2     | a4       | 02/10/2019 | 10/10/2019 |
|                       4 | p2     | a2       | 01/10/2019 | 01/12/2019 |
|                       5 | p3     | a3       | 01/06/2019 | 01/07/2019 |
|                       5 | p4     | a3       | 01/01/2019 | 20/12/2020 |
|                       6 | p5     | a3       | 01/01/2017 | 20/12/2020 |
|                       7 | p5     | a3       | 20/03/2017 | 20/10/2019 |
+-------------------------+--------+----------+------------+------------+

results:

+-----------+----------+------------+------------+
| people_id | activity |   start    |    end     |
+-----------+----------+------------+------------+
| p1        | a3       | 02/10/2019 | 10/10/2019 |
| p2        | a3       | null       | null       |
| p3        | a3       | null       | null       |
| p5        | a3       | 01/01/2017 | 20/12/2020 |
| p5        | a3       | 20/03/2017 | 20/10/2019 |
+-----------+----------+------------+------------+

Upvotes: 0

Views: 82

Answers (3)

Michael Tobisch
Michael Tobisch

Reputation: 1098

SELECT
   pa.people_id,
   pa.activity_type_id,
   al.[start],
   al.[end]
FROM
(
   SELECT
      p.people_id,
      a.activity_type_id
   FROM
      people p, activity a
   WHERE
      p.people_id <> 'p4'
      AND a.activity_type_id = 'a3'
) pa
LEFT JOIN activity_link al ON al.people = pa.people_id AND al.activity = pa.activity_type_id AND al.[start] <= CAST('2019-10-04' AS date) AND al.[end] >= CAST('2019-10-04' AS date)
WHERE
   (al.[start] IS NOT NULL AND al.[end] IS NOT NULL)
   OR al.[start] IS NULL
   OR al.[end] IS NULL

Upvotes: 1

Mehmet Resul Akyuz
Mehmet Resul Akyuz

Reputation: 95

if you don't want see null reference . You can USE NVL(start,0) for null reference

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

You can try using left join

select a.people_id,activity,start,ednd from peopletable a 
left join ActivityLink b on a.people_id=b.people_id 
               and '04/10/2019'>=start and '04/10/2019'<=end and activity='a3'
where a.people_id!='p4'

Upvotes: 0

Related Questions