CodeIgniter Forums
Sql select statement with two foreign key taken from the same table - 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: Sql select statement with two foreign key taken from the same table (/showthread.php?tid=53343)

Sql select statement with two foreign key taken from the same table - El Forum - 07-20-2012

[eluser]Crackarjack[/eluser]
create table
Code:
CREATE TABLE IF NOT EXISTS `bf_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL DEFAULT '4',
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `email` varchar(120) NOT NULL,
  `username` varchar(30) NOT NULL DEFAULT '',
  `password_hash` varchar(40) NOT NULL,
  `reset_hash` varchar(40) DEFAULT NULL,
  `salt` varchar(7) NOT NULL,
  `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_ip` varchar(40) NOT NULL DEFAULT '',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `street_1` varchar(255) DEFAULT NULL,
  `street_2` varchar(255) DEFAULT NULL,
  `city` varchar(40) DEFAULT NULL,
  `zipcode` varchar(20) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `reset_by` int(10) DEFAULT NULL,
  `country_iso` char(2) DEFAULT 'US',
  `state_code` char(4) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;



create another table.

Code:
CREATE TABLE IF NOT EXISTS `bf_job_records` (
  `job_ID` int(11) NOT NULL AUTO_INCREMENT,
  `project_name` varchar(255) NOT NULL,
  `job_date` date NOT NULL,
  `job_propsal_date` date NOT NULL,
  `client_ID` int(11) NOT NULL,
  `job_po_no` varchar(255) NOT NULL,
  `job_po_date` date NOT NULL,
  `job_project_details` varchar(255) NOT NULL,
  `job_user_lead_ID` int(11) NOT NULL,
  `job_user_coordinator_ID` int(11) NOT NULL,
  `domain_ID` int(11) NOT NULL,
  `hosting_ID` int(11) NOT NULL,
  `email_record_ID` int(11) NOT NULL,
  `marketing_person_ID` int(11) NOT NULL,
  PRIMARY KEY (`job_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;


Code:
$this->db->select(
  'lead.username,
      coor.username,
      J.job_ID,
  J.project_name,
  P.payment_phase,
  E.email_password');
  
  $this->db->from('job_records  J', 'payment AS P', 'email_records AS E');
  $this->db->join('payment AS P', 'P.job_ID = J.job_ID');
  $this->db->join('email_records AS E', 'E.job_ID = E.job_ID');
  $this->db->join('users AS lead','J.job_user_lead_ID=lead.id');
  $this->db->join('users AS coor','J.job_user_coordinator_ID=coor.id');


i am using bonfire.. so there is table prefix bf_ you can reomove it while running without bonfire.

thank you.

let me know if there is any queries

Sql select statement with two foreign key taken from the same table - El Forum - 07-20-2012

[eluser]Crackarjack[/eluser]
Code:
$this->db->select(
  'lead.username as username1,
      coor.username as username2,
      J.job_ID,
  J.project_name,
  P.payment_phase,
  E.email_password');
  
  $this->db->from('job_records  J', 'payment AS P', 'email_records AS E');
  $this->db->join('payment AS P', 'P.job_ID = J.job_ID');
  $this->db->join('email_records AS E', 'E.job_ID = E.job_ID');
  $this->db->join('users AS lead','J.job_user_lead_ID=lead.id');
  $this->db->join('users AS coor','J.job_user_coordinator_ID=coor.id');
  $result = $this->db->get();
  $test = $result->result();
  $str = $this->db->last_query();