SQL HAVING COUNT and JOIN
- by user1833274
I have tried to this query: What are the doctors that work on less than 2 Hospitals.
I have these tables:
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)
);
I tried with this:
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) < 2
;
