Select rows which are not present in other table

xain819

how can i do this

SELECT e.id ,e.Badgenumber, e.Name FROM pcc_employee e WHERE NOT EXISTS (SELECT 1 FROM pcc_empcollege c WHERE e.Badgenumber = c.empid)

to

$out['pcc_employee'] = $db
        ->query( 'select', 'pcc_employee' )
            ->get( 'pcc_employee.Badgenumber as value, pcc_employee.Name as label' )
            ->where( 'Badgenumber','2005','=' )
            ->exec()
            ->fetchAll();

allan

Try:

$db->selectDistinct(
   'pcc_employee',
   'Badgenumber as value, Name as label'
   array( 'Badgenumber', '2005' ),
   'label asc'
)

Documentation for selectDistinct is available here: http://editor.datatables.net/docs/Editor-1.3.1/php/class-DataTables.Database.html#_selectDistinct

Regards,
Allan

xain819

Thanks i got the Idea i used

$out['pcc_employee'] = $db
        ->query( 'select', 'pcc_employee' )
                ->get( 'Badgenumber as value, Name as label' )
                ->where( 'Badgenumber','2005','=' )
                ->exec()
                ->fetchAll();

Thanks alot :)

xain819

oh and how can i do this

SELECT e.Name FROM pcc_employee e WHERE NOT EXISTS (SELECT 1 FROM pcc_empcollege c WHERE e.Badgenumber = c.empid)

i have no idea :|

allan

You can use the sql() method to be able to run raw SQL: http://editor.datatables.net/docs/Editor-1.3.1/php/class-DataTables.Database.html#_sql

Allan

xain819

okey but i don't know how to use it to put it in a drop box

$our['pcc_employee'] = $db
            ->sql('SELECT e.id ,e.Badgenumber, e.Name FROM pcc_employee e WHERE NOT EXISTS (SELECT 1 FROM pcc_empcollege c WHERE e.Badgenumber = c.empid) order by e.Name')
            ->get( 'e.Badgenumber as value, e.Name as label' )
            ->fetchAll();

so if u could kindly teach me how :). here is my sample code..