Welcome Guest, Not a member yet? Register   Sign In
subquery problem in active record
#1

[eluser]mardon[/eluser]
Hi all,

i have
Code:
function getAllActive($limit = 0, $offset = 0) {

    $today = date('Y-m-d');
    $this->db->select('certs.*');
    $this->db->select('users.prijmeni as jmeno');
    $this->db->from('certs');
    $this->db->join('users', 'certs.id_user=users.id');
    $this->db->where('konec >= ',$today);
    $this->db->where('`id` NOT IN (SELECT `id_cer` FROM `revokace`)', NULL, FALSE);
    $this->db->limit($limit,$offset);
    $q = $this->db->get();
    if ($q->num_rows() > 0) {
        foreach ($q->result() as $row) {
            $data[] = $row;
        }
        print_r($data);die;
        return $data;
    }

}

that produce teh error:

Code:
Error Number: 1052

Column 'id' in IN/ALL/ANY subquery is ambiguous

SELECT certs.*, users.prijmeni as jmeno FROM (certs) JOIN users ON certs.id_user=users.id WHERE konec >= '2011-05-30' AND id NOT IN (SELECT id_cer FROM revokace) LIMIT 3
#2

[eluser]Gerep[/eluser]
Hi mardon,

The 'id' column belongs to which table? Use the users.id or certs.id on your last db->where.
#3

[eluser]mardon[/eluser]
id belong to certs.id
#4

[eluser]Gerep[/eluser]
So change to this:

$this->db->where('`certs.id` NOT IN (SELECT `id_cer` FROM `revokace`)', NULL, FALSE);

I think it works well Wink
#5

[eluser]mardon[/eluser]
This I try but the error is the same:

Unknown column 'certs.id' in 'IN/ALL/ANY subquery'

SELECT `certs`.*, `users`.`prijmeni` as jmeno FROM (`certs`) JOIN `users` ON `certs`.`id_user`=`users`.`id` WHERE `konec` >= '2011-06-01' AND `certs.id` NOT IN (SELECT `id_cer` FROM `revokace`) LIMIT 3
#6

[eluser]danmontgomery[/eluser]
Column 'id' in IN/ALL/ANY subquery is ambiguous
and
Unknown column ‘certs.id’ in ‘IN/ALL/ANY subquery’

are the same?

it should be `certs`.`id` or just certs.id, not `certs.id`
#7

[eluser]mardon[/eluser]
Quote:it should be `certs`.`id` or just certs.id, not `certs.id`

yes, you are right, thank you




Theme © iAndrew 2016 - Forum software by © MyBB