Re: Matching several rows

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