Join query problem

[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)