CodeIgniter Forums
Using join - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Using join (/showthread.php?tid=13074)



Using join - El Forum - 11-10-2008

[eluser]Marcelo Kanzaki[/eluser]
Hello everybody,

here is the situation:

I have 3 tables in my database - Events, Consultants and Locations.

To generate an event, i need pick one (or more) consultant from the Consultants table along with a location from the Locations table.

To handle the one_to_many relationship between Events and Consultants i've created a fourth table called Consultants_Events, wich has the event_id and the consultant_id that allows me to add more than one consultant per event.

Quote:***** My question is how do i list Events now? *****

I could simply use

Code:
$this->db->get('events');
$this->db->join('locations', 'locations.id = events.location_id');

But what about the consultants? Since there can be more than one per event, how do i list them?


Using join - El Forum - 11-10-2008

[eluser]Xeoncross[/eluser]
I haven't tried it - but just use several joins.

Code:
$this->db->join('consultants', 'consultants.id = consultants_events.consultant_id');
$this->db->join('events', 'events.id = consultants_events.event_id');
$this->db->join('locations', 'locations.id = events.location_id');

//Fetch
$this->db->get('consultants_events');



Using join - El Forum - 11-10-2008

[eluser]crumpet[/eluser]
when I do this i use two seperate queries - one for events and one for consultants...
inefficient but i dont know a better way to handle it.