title: SQL HAVING COUNT and JOIN
body: <p>I have tried to this query: What are the doctors that work on less than 2 Hospitals. But the result isn't what I expected.</p>
  
  <p>I have these tables:</p>
  
  <pre><code>CREATE TABLE Hospital (
      hid INT PRIMARY KEY,
      name VARCHAR(127) UNIQUE,
      country VARCHAR(127),
      area INT
  );
  CREATE TABLE Doctor (
      ic INT PRIMARY KEY,
      name VARCHAR(127),
      date_of_birth INT,
  );
  CREATE TABLE Work (
      hid INT,
      ic INT,
      since INT,
      FOREIGN KEY (hid) REFERENCES Hospital (hid),
      FOREIGN KEY (ic) REFERENCES Doctor (ic),
      PRIMARY KEY (hid,ic)
  );
  </code></pre>
  
  <p>I tried with this:</p>
  
  <pre><code>SELECT DISTINCT D.ic 
      FROM Doctor D, Work W 
      JOIN Hospital H ON (H.hid = W.hid)
      WHERE D.bi = W.bi
      GROUP BY (D.ic)
      HAVING COUNT(H.hid) &lt; 2
  ;    
  </code></pre>
  
  <p>Thanks.</p>