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