0

hi every one have you any idea about subquery in codeigniter here is my query i want to convert it into codeigniter 

SELECT question . * 
FROM question
WHERE question.id NOT 
              IN ( SELECT id
                   FROM answers )
Fopa Léon Constantin
  • 11,863
  • 8
  • 48
  • 82
user2945316
  • 1
  • 4
    http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record – Sam Mar 28 '14 at 22:31

3 Answers3

0

First make sure you have correct relation between question and answers because you are matching the question id with the id of answer table its confusing,also you can get rid of your subquery and use join instead

$this->db->select('q.*');
$this->db->from('question q');
$this->db->join('answers a','q.id=a.id','LEFT');/* make sure second parameter should match the question id from answer table */
$this->db->where('a.id IS NULL',null,FALSE);
$query = $this->db->get();

Active Record

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

Subqueries can be placed in a string where clause.

$this->db->select("question.*");
$this->db->where("question.id NOT IN (SELECT id FROM answers)");
$this->db->get('question');
YouSer
  • 393
  • 3
  • 12
0

$this->db->select('question.*');

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

$this->db->where_not_in('question.id','(SELECT id FROM answers)',false);

$result = $this->db->get()->result_array();

print_r($result);

Dilip Kalsariya
  • 1
  • 2
  • Hello, welcolme on StackOverflow ! Can you please explain your proposition ? Also, add 4 spaces at the beginning of each line of code to that code become prettier on this site ! (> [edit](https://stackoverflow.com/posts/48337998/edit)) Thank you ! – NatNgs Jan 19 '18 at 09:54