CODEWITHSUNDEEP
sql-servercodeigniter
user2447440
user2447440

Reputation:

how i can use multiple select queries in codeigniter?

how i can convert this query in codeigniter 

select COUNT(*) 
from Retailers
where ID not In (select RetailerID from RetailerGroups)

i tried this 

$this->db->where_not_in('ID',$this->db->query('select RetailerID from 
 RetailerGroups'));

 $query = $this->db->get('Retailers');

but it prints 

Error Number: 42000

[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'.

SELECT * FROM Retailers WHERE ID NOT IN ()

Filename: D:\Published\faber\core\database\DB_driver.php

Line Number: 330

please help

Upvotes: 0

Views: 2324

Answers (2)

Nil'z
Nil'z

Reputation: 7475

Try this once,

$idRs  = $this->db->select('RetailerID')->get('RetailerGroups')->result_array();
if( isset( $idRs ) && count( $idRs ) > 0 ){
    foreach( $idRs as $each ){
        $ids[]  = $each['RetailerID'];
    }
    echo "total :".$countRs    = $this->db->from('Retailers')->where_not_in('ID', $ids)->count_all_results();
}

Upvotes: 0

Art K.
Art K.

Reputation: 26

I believe you can try use that syntax: 

$this->db->select('count(*)')->from('Retailers');
$this->db->where('ID not in (select RetailerID from RetailerGroups)', NULL, FALSE);

The ,NULL,FALSE in the where() tells CodeIgniter not to escape the query.

Or you can just use JOIN with this query instead of subquery.

Upvotes: 1

Related Questions