Select all except some rows

Question

let's say i have table with this structure:

  id      model       color
------  --------    ----------
   1      Ford           yellow
   2      Ford           green
   3      Ford           red
   4      Ford           yellow
   5      Subaru         yellow
   6      Subaru         red

I need to make a query, which returns me every car in list, except for yellow ford's. Can somebody help?

Answer 1

If you want to deselect some rows dynamically, you may use it:

SELECT * FROM `table_1` WHERE id NOT IN (SELECT id FROM `table_2` WHERE column_name='value')

NOTE: Here, id is a column_name, which both tables have in common.

Good luck. :)

Answer 2

Hope this will help you.

    $this->db->where('model !=','FORD' AND 'color!=','yellow');
    $query= $this->db->get('table_name');
    $res = $query->result_array();
    return $res;

Answer 3

... WHERE NOT (model = 'Ford' AND color = 'yellow')

Answer 4

Use Color<> "yellow" and model <> "Ford" as your where clause

Answer 5

In addition to Foo Bah's answer,

if you have null column values like that;

NAME      COLOR

Ford      green               
Ford      red                 
Subaru    yellow              
Subaru    red                 
Subaru    NULL
Ford      NULL              

you need to use ISNULL() function to bring null column values

... WHERE  NOT (ISNULL(NAME,'') = 'Ford' AND ISNULL(COLOR,'') = 'yellow')