[eluser]ascotan[/eluser]
Call me crazy but it sounds like your model is a little strange.
So basically you have a ternary relationship between incidents, elevators and clients. You should think of this as 3 binary relationships.
[incidents] --<caused by>-- [clients] ---<rides a> --- [elevators]
| |
|-------------------<caused by>------------------|
incidents to clients = 1 to 1
incidents to elevators = 1 to 1
elevators to clients = 1 to N
So you'd have:
incidents (pkey, elevatorkey, clientkey) (1 to 1 can put pkey in either relation but seems incidents is where it goes)
elevators (pkey)
clients (pkey, elevatorkey) (1 to N)
right?
(You could also have a single foreign key field in incidents and have a type as well)
So to query incidents you'd:
SELECT elevatorkey, clientkey
FROM incidents
if clientkey is null you'd then run
SELECT clientkey
FROM clients
WHERE clients.elevatorkey = elevatorkey
to pull all the clients in the elevator
or you could
SELECT i.elevatorkey, i.clientkey, c.clientkey
FROM incidents i
LEFT JOIN clients c ON c.elevatorkey=i.elevatorkey
but of course this would produce multiple records of:
(ekey, NULL, ckey) per elevator incident.
otherwise you'd get a single record of
(NULL, ckey, NULL)