[El Forum]

[eluser]andriu[/eluser]
Hi guys

I would like to start of to thank the users on this forum!!
And also say that I find CI amazing.

I'm pretty new to mysql and just discovered the power of "join"!!

Now i'm facing a problem

Ive got three tables

Code:

incidents
elevators
clients

a incident is connected either to a client or a elevator
a elevator is always connected to a client

I try to join them like this:

Code:

$this->db->from('incidencias');
$this->db->join('elevators', 'incidents_elevator_id = elevator_id', 'LEFT');
$this->db->join('clients', 'incidents_client_id = client_id', 'LEFT');

In an incident I either save the elevator_id or the client_id depending on the type of incident.
(I know start to think that this was an error)

And now i want to be able to select the "elevator" or "client" depending on the incidencia
but if it is related to the elevator I also want to select the client.

With the code above I just get either the client or the elevator.

Is what im trying to do impossible with my table structure?

My old way of doing it is running another query for every incident!!

(a eleveator could change owner along the way and I would then want to select the new client)

Thanks in advance for any help!

[El Forum]

[eluser]andriu[/eluser]
I suppose what Im asking is:
is there a way to join a table twice??

[El Forum]

[eluser]nevsie[/eluser]
this is something i was doing the other day, but i ran into some obsticles...

you can look into UNION which allows you to merge results
or you can look into subsetting your queries to perform what you trying to do.

[El Forum]

[eluser]danmontgomery[/eluser]
[quote author="andriu" date="1267127986"]With the code above I just get either the client or the elevator.[/quote]

You're mistaken... With the code above you get the data from all 3 tables. With active record when you don't specify a select(), it defaults to "SELECT *". When you join tables and SELECT *, it selects all columns from all tables (The values will be NULL when join conditions aren't met). You might have some collisions in column names, but you are getting all of the data. Alternately, you can just specify which table's data you want.

Code:

$this->db->select("incedencias.*, elevators.some_data, clients.some_data");

You can then programmatically determine which fields are null.

To directly answer your question, you can join a table twice (as many times as you want actually, you can even join a table to itself), but I don't think that's what you want to do here.

[El Forum]

[eluser]andriu[/eluser]
Thanks for the replies

Noctrum your correct that I get the data from all three tables.

What i want to do something similar to this i believe:

Code:

$this->db->from('incidencias');


$this->db->join('clients', 'incidents_client_id = client_id', 'LEFT');

or

$this->db->join('elevators', 'incidents_elevator_id = elevator_id', 'LEFT');
$this->db->join('clients', 'incidents_client_id = elevators.client_id', 'LEFT');

Which (in my mind =)) would get me the:
elevator and the client if the incident is related to a elevator.

and just the client if the incident is related to a client

But Im not sure how to join the client table again but the second time with the elevator table...

forgot to make this clear

I only have either the elevator id or the client id in the incident table not both at the same time as i would no want to select the wrong client if the owner of the elevator changes

[El Forum]

[eluser]danmontgomery[/eluser]
The point is that you already have that information... All you have to do is check what's NULL when you get it. By trying to re-join the table you're only costing yourself a headache.

Code:

$if($row->elevator_id == NULL) {
  // Do some work on the client
} else {
  // Do some work on the elevator
}

[El Forum]

[eluser]andriu[/eluser]
Ok.. Sold
That works!

Thanks for the help!

[El Forum]

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

[El Forum]

[eluser]Denarius[/eluser]
I wonder if it might be worth your while to state the business requirement that your application is modelling. I'm struggling to envisage a situation. What's an incident? Why can't an incident involve a client in an elevator (elevator falls 10 storeys with people trapped inside maybe?)? :bug:

Update: Are you in the elevator repair business? If so I'm wondering whether this premise stands up. I've got thoughts on this, but am reluctant to post them if it turns out they're irrelevant. :roll: