0

I have tried to this query:What are the names of the hospitals where work all the doctors that work on it's country. But the result isn't what I expected and it has a syntax error.

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 H.name
FROM Hospital H, Doctor D, Work W
WHERE H.hid = W.hid 
AND   D.ic = W.ic 
AND NOT EXISTS (SELECT DISTINCT H2.name 
                FROM Hospital H2
                WHERE H2.country = H.country AND W.hid = H2.hid AND D.ic != W.ic)

What I was thinking was to look for an hospital H2 in the same country but where the doctor D doesn't work.

Thanks.

gdoron
  • 147,333
  • 58
  • 291
  • 367
tomss
  • 269
  • 4
  • 6
  • 12
  • The question is not clear, and the columns' names: `hid`, ic` are awful names. – gdoron Nov 27 '12 at 18:19
  • Find a hospital a given doctor hasn't worked in, that's in a country the doctor has worked in? Or which doctors haven't worked in a given hospital that have worked in the country where the hospital is? ... and the column names _are_ awful – mcalex Nov 27 '12 at 18:23
  • ic (identity card) Hid (Hospital identity) – tomss Nov 27 '12 at 18:26
  • What I want is to find the hospitals, where doctors that work in the other hospitals (in the same country), work – tomss Nov 27 '12 at 18:28

0 Answers0