From: Milen A. Radev <mradev@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Difficulties with a master-detail query
Date: Mon, 19 Sep 2005 20:29:40 +0300
Message-ID: <32c009ea050919102924653bfa@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mradev@gmail.com
Subject: Re: Difficulties with a master-detail query
In-Reply-To: <32c009ea050919102924653bfa@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox