pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifficulties with a master-detail query
2+ messages / 2 participants
[nested] [flat]
* Difficulties with a master-detail query
@ 2005-09-19 17:29 Milen A. Radev <mradev@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Milen A. Radev @ 2005-09-19 17:29 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Difficulties with a master-detail query
@ 2005-09-23 14:13 Bruno Wolff III <bruno@wolff.to>
parent: Milen A. Radev <mradev@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2005-09-23 14:13 UTC (permalink / raw)
To: milen@radev.net; +Cc: pgsql-sql
On Mon, Sep 19, 2005 at 20:29:40 +0300,
"Milen A. Radev" <mradev@gmail.com> wrote:
>
> 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?
That seems like a reasonable way to do things. The other way to do this is
to join the employee_skills table once for each skill. However, I expect
the above approach to be faster.
If you want to use skill names you can join against the skill table.
I don't think is an automatic way to tie the count() comparison to the number
of different skills being checked. But if you are doing this through an app
it already has to generate the IN list, so it shouldn't be hard to make it
figure out what to use to campare the count to.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-09-23 14:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-19 17:29 Difficulties with a master-detail query Milen A. Radev <mradev@gmail.com>
2005-09-23 14:13 ` Bruno Wolff III <bruno@wolff.to>
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