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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq