Select rows which are not present in other table

Select rows which are not present in other table

xain819xain819 Posts: 15Questions: 4Answers: 1
edited June 2014 in Free community support

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();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 55,114Questions: 1Answers: 8,654 Site admin
    Answer ✓

    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

  • xain819xain819 Posts: 15Questions: 4Answers: 1
    edited June 2014

    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 :)

  • xain819xain819 Posts: 15Questions: 4Answers: 1

    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 :|

  • allanallan Posts: 55,114Questions: 1Answers: 8,654 Site admin

    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

  • xain819xain819 Posts: 15Questions: 4Answers: 1

    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..

This discussion has been closed.