From: Michael Glaesemann <grzm@myrealbox.com>
To: Ivan Steganov <istegan6@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Matching several rows
Date: Wed, 18 Jan 2006 14:04:16 +0900
Message-ID: <DBEB2BBA-60E7-41FF-B409-D95E605B8BB0@myrealbox.com> (raw)
In-Reply-To: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com>
References: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com>
On Jan 18, 2006, at 13:43 , Ivan Steganov wrote:
> A table URIGHTS which stores an ID and the RIGHTs this ID has. One
> ID may have many rights and accordingly records in table, sample:
> Now I need to find out which IDs have, say rights 2 AND 5 AND 10.
select "ID"
from "URIGHTS" as right_2
join "URIGHTS" as right_5 using ("ID")
join "URIGHTS" as right_10 using ("ID")
where right_2."RIGHT" = 2
and right_5."RIGHT" = 5
and right_10."RIGHT" = 10
or
select "ID"
from (
select "ID"
from "URIGHTS"
where "RIGHT" = 2
) as right_2
join (
select "ID"
from "URIGHTS"
where "RIGHT" = 5
) as right_5 using ("ID")
join (
select "ID"
from "URIGHTS"
where "RIGHT" = 10
) as right_10 using ("ID")
Simple is in the eye of the beholder. You might want to compare the
EXPLAIN ANALYZE output to see if there are any significant
differences between these queries.
Michael Glaesemann
grzm myrealbox com
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: grzm@myrealbox.com, istegan6@gmail.com
Subject: Re: Matching several rows
In-Reply-To: <DBEB2BBA-60E7-41FF-B409-D95E605B8BB0@myrealbox.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