In-Reply-To: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com>
References: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com>
Mime-Version: 1.0 (Apple Message framework v746.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <DBEB2BBA-60E7-41FF-B409-D95E605B8BB0@myrealbox.com>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@myrealbox.com>
Subject: Re: Matching several rows
Date: Wed, 18 Jan 2006 14:04:16 +0900
To: Ivan Steganov <istegan6@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