Difficulties with a master-detail query - Mailing list pgsql-sql

Hi, list!

You have most probably met the same problem:

I have a master table, describing the objecs I'm interested in - let's
say employees. I have a details table, defining all possible values of
one of the properties of the objects - let's say languages spoken. And
of course I have a table desribing the table the connection between
the latter two - N:N (fairly standard solution).

Here is the scheme:

CREATE TABLE employee ( employee_id serial PRIMARY KEY, name varchar(20)
);

CREATE TABLE skill ( skill_id serial PRIMARY KEY, name varchar(20)
);

CREATE TABLE employee_skill ( employee_id integer, skill_id integer, CONSTRAINT employee_skill_employee_id_fkey FOREIGN
KEY(employee_id) 
REFERENCES employee(employee_id), CONSTRAINT employee_skill_skill_id_fkey FOREIGN KEY (skill_id)
REFERENCES skill (skill_id), CONSTRAINT employee_skill_employee_id_key UNIQUE (employee_id, skill_id)
);


I would like to get all employees, who speak two specified languages
(say german and french). The following query gives me that, bu I don't
like it (see for yourself):


SELECT е.employee_id, е.name, COUNT(s.skill_id)
FROM employee AS e
INNER JOIN employee_skill AS es
ON e.employee_id=es.employee_id
INNER JOIN skill AS s
ON s.skill_id=es.skill_id AND s.skill_id IN (1, 2)
GROUP BY e.employee_id, e.name
HAVING COUNT(s.skill_id)>=2;


Here "(1, 2)" are the IDs for those predefined two languages, got from
the "skill" table. Аnd that two in "COUNT(s.skill_id)>=2" is there
because the count of the languages.


Any ideas for simpler and more universal query?


Please CC me, because I'm not subscribed.

--
Milen A. Radev