How to INNER JOIN 3 tables using CodeIgniter?

Today, We want to share with you inner join codeigniter.In this post we will show you codeigniter subquery in join, hear for left join in codeigniter we will give you demo and example for implement.In this post, we will learn about Join Query In Codeigniter Example Tutorial with an example.

CodeIgniter Inner Join Query Example

CodeIgniter Inner Join Query:

$this->db->select('MemberID, MemberName, CartName, GSTNO');
$this->db->from('Members');
$this->db->join('Cart', 'Cart.CartID = Members.CartID');
$query = $this->db->get();

// Produces SQL
// SELECT MemberID, MemberName, CartName, GSTNO FROM Members JOIN Cart on Cart.CartID = Members.CartID;

Multiple Inner Join Query in CodeIgniter:

$this->db->select('MemberID, MemberName, LevelName, CartName, GSTNO');
$this->db->from('Members');
$this->db->join('Level', 'Level.LevelID = Members.LevelID');
$this->db->join('Cart', 'Cart.CartID = Members.CartID');
$query = $this->db->get();

// Produces SQL
// SELECT MemberID, MemberName, LevelName, CartName, GSTNO FROM Members JOIN Level ON Level.LevelID = Members.LevelID JOIN Cart ON Cart.CartID = Members.CartID;

CodeIgniter Inner Join Query with Where Clause:

$this->db->select('MemberID, MemberName, LevelName, CartName, GSTNO');
$this->db->from('Members');
$this->db->join('Level', 'Level.LevelID = Members.LevelID');
$this->db->join('Cart', 'Cart.CartID = Members.CartID');
$this->db->where('LevelName', 'Self Development');
$query = $this->db->get();

// Produces SQL
// SELECT MemberID, MemberName, LevelName, CartName, GSTNO FROM Members JOIN Level ON Level.LevelID = Members.LevelID JOIN Cart ON Cart.CartID = Members.CartID WHERE LevelName = 'Self Development';

CodeIgniter Inner Join Query Example:

Create MySQL Database:

CREATE TABLE IF NOT EXISTS `Level` (
  `LevelID` int(8) NOT NULL AUTO_INCREMENT,
  `LevelName` varchar(30) NOT NULL,
  PRIMARY KEY (`LevelID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Level` (`LevelID`, `LevelName`) VALUES
(1, 'Self Development'),
(2, 'Literature'),
(3, 'Science');

CREATE TABLE IF NOT EXISTS `Cart` (
  `CartID` int(8) NOT NULL AUTO_INCREMENT,
  `CartName` varchar(40) NOT NULL,
  PRIMARY KEY (`CartID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Cart` (`CartID`, `CartName`) VALUES
(1, 'Robert Greene'),
(2, 'Napoleon Hill'),
(3, 'Eckhart Tolle'),
(4, 'Charlotte Bronte');

CREATE TABLE IF NOT EXISTS `Members` (
  `MemberID` int(8) NOT NULL AUTO_INCREMENT,
  `MemberName` varchar(100) NOT NULL,
  `LevelID` int(8) NOT NULL,
  `CartID` int(8) NOT NULL,
  `GSTNO` varchar(30) NOT NULL,
  PRIMARY KEY (`MemberID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `Members` (`MemberID`, `MemberName`, `LevelID`, `CartID`, `GSTNO`) VALUES
(1, 'The 48 Laws of Power', 1, 1, 'GSTNO-13: 825-1491918661'),
(2, 'Think and Grow Rich', 1, 2, 'GSTNO-13: 825-0321784070'),
(3, 'The Power of Now', 1, 3, 'GSTNO-13: 825-1449363758'),
(4, 'Jane Eyre', 2, 4, 'GSTNO-13: 825-1449392772');

Joindemo_model.php

db->select('MemberID, MemberName, LevelName, CartName, GSTNO');
        $this->db->from('Members');
        $this->db->join('Level', 'Level.LevelID = Members.LevelID');
        $this->db->join('Cart', 'Cart.CartID = Members.CartID');
        $query = $this->db->get();
        return $query->result();
    }
}
?>

application/controllers/Joindemo.php

load->helper('url');
        $this->load->database();
        $this->load->model('joindemo_model');
    }

    public function index()
    {
        $data['members'] = $this->joindemo_model->getMembers();           
        $this->load->view('joindemo_view',$data);
    }
}
?>

application/views/Joindemo_view.php

    
    
    
    ">



    

        

            

                

                    

                        
#
                        
Member Name
                        
Level Name
                        
Cart Name
                        
GSTNO
                    
                
                

                
                

                    

                    
MemberName; ?>
                    
LevelName; ?>
                    
CartName; ?>
                    
GSTNO; ?>
                
                
                
            
        
    

I hope you get an idea about inner join codeigniter.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.