Welcome Guest, Not a member yet? Register   Sign In
Sql select statement with two foreign key taken from the same table
#1

[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
#2

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




Theme © iAndrew 2016 - Forum software by © MyBB