query a parent child relationship in sql

Question

I am working on the above ER diagram. Now an event has complete participation in the child that means an event is either an observation, intervention or a diagnosis but a single event can never exist in more than one child tables.

So event table has:

id | event_type

1  | diagnosis
2  | diagnosis
3  | observation
4  | observation
5  | intervention

Diagnosis table has:

id | name  | event_FK

1  |cancer | 1
2  |none   | 2

Observation table has:

id|    name            | event_FK

1 |progressive disease | 3
2 |none                | 4

I want to run the following query on this model.

"Find all events that have diagnosis.name=cancer and observation.name=progressive disease."

The result should contain event id 1 and 3 

I am trying to do it with joins but can't select only the events which are either of type diagnosis or intervention.I can join the two tables as

select * from event e1
left join diagnosis d on d.event_fk = e1.id 
left join observation o on o.event_fk e1.id

but this is clearly wrong as the resulting table still has data from 'intervention' table. I need to know how can i eliminate data from this 'intervention' table? also how to write a efficient query for such cases.

Thanks

Answer 1

Since the WHERE clause is executed after the LEFT JOIN, you can take advantage of that and add a WHERE clause to the query filtering out anything that doesn't have those values:

Select      E.*
From        Event       E
Left Join   Diagnosis   D   On  D.Event_FK = E.Id
Left Join   Observation O   On  O.Event_FK = E.Id
Where       D.Name = 'Cancer'
Or          O.Name = 'Progressive Disease'

Results:

Id  event_type
1   diagnosis
3   observation

Note: Including the right-hand table of a LEFT JOIN in the WHERE clause typically leads to unexpected results as it essentially transforms the LEFT JOIN into an INNER JOIN by filtering out the NULL values. But in this situation, that's exactly what we want to do.