From: Bruno Wolff III <bruno@wolff.to>
To: milen@radev.net
Cc: pgsql-sql@postgresql.org
Subject: Re: Difficulties with a master-detail query
Date: Fri, 23 Sep 2005 09:13:16 -0500
Message-ID: <20050923141316.GB2298@wolff.to> (raw)
In-Reply-To: <32c009ea050919102924653bfa@mail.gmail.com>
References: <32c009ea050919102924653bfa@mail.gmail.com>
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.
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: bruno@wolff.to, milen@radev.net
Subject: Re: Difficulties with a master-detail query
In-Reply-To: <20050923141316.GB2298@wolff.to>
* 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