• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Join query problem

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


Messages In This Thread
Join query problem - by El Forum - 02-25-2010, 07:59 AM
Join query problem - by El Forum - 02-25-2010, 08:38 AM
Join query problem - by El Forum - 02-25-2010, 08:48 AM
Join query problem - by El Forum - 02-25-2010, 08:49 AM
Join query problem - by El Forum - 02-25-2010, 09:04 AM
Join query problem - by El Forum - 02-25-2010, 09:30 AM
Join query problem - by El Forum - 02-25-2010, 10:00 AM
Join query problem - by El Forum - 02-25-2010, 10:05 AM
Join query problem - by El Forum - 03-03-2010, 06:00 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.