title: mySQL - difference between two tables
body: <p>I have two tables as follows:</p>
  
  <pre><code>CREATE TABLE `keywords_by_city` (
    `idKEYWORD` int(11) NOT NULL,
    `CITY` varchar(45) NOT NULL);
  
  CREATE TABLE `city` (
    `idCITY_NAME` varchar(50) NOT NULL DEFAULT ''
  );
  </code></pre>
  
  <p>I want to get a list of all the cities in which idKeyword=781 is not present.</p>
  
  <p>I tried creating the query as follows but I don't think its correct:</p>
  
  <pre><code>SELECT cty.`idCITY_NAME`
  FROM `keywords_by_city` kbc
  LEFT JOIN `city` cty ON cty.`idCITY_NAME` = kbc.`CITY`
  WHERE kbc.`idKEYWORD` IS NULL
  AND kbc.`idKEYWORD` = 781 
  </code></pre>
  
  <p>I also tried the following:</p>
  
  <pre><code>SELECT `CITY`
  FROM `keywords_by_city` kbc
  WHERE kbc.`idKEYWORD` = 781
  AND kbc.`CITY` NOT IN (SELECT `idCITY_NAME` FROM `city`);
  </code></pre>
  
  <p>neither of these seem to work. Can someone please help. I would prefer a solution without a sub query if possible.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I am using the following data:</p>
  
  <pre><code>INSERT INTO keywords_by_city (idKEYWORD, CITY)
  VALUES (781, 'NYC'), (266855, 'NYC'),
  (266856, 'NYC'), (266857, 'NYC'),
  (266858, 'NYC'), (266859, 'NYC');
  
  INSERT INTO `city`
  (`idCITY_NAME`)
  VALUES
  ('NYC'),('Jersey City'),
  ('San Jose'),('Albany');
  </code></pre>