Date: Fri, 23 Sep 2005 09:13:16 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: milen@radev.net
Cc: pgsql-sql@postgresql.org
Subject: Re: Difficulties with a master-detail query
Message-ID: <20050923141316.GB2298@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>, milen@radev.net,
	pgsql-sql@postgresql.org
References: <32c009ea050919102924653bfa@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <32c009ea050919102924653bfa@mail.gmail.com>
User-Agent: Mutt/1.5.6i

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.