Skip site navigation (1) Skip section navigation (2)

Re: Simple Query?

From: Osvaldo Rosario Kussama <osvaldo_kussama(at)yahoo(dot)com(dot)br>
To: Koen Bok <koen(at)madebysofa(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Simple Query?
Date: 2007-09-12 14:22:12
Message-ID: 46E7F614.8070201@yahoo.com.br (view raw or flat)
Thread:
Lists: pgsql-sql
Koen Bok escreveu:
> I am doing some optimization on our search, but I need some advise...
> 
> table: item
> 
> id            name
> --------------------------------------
> 1            iPod
> 2            Zune
> 3            Walkman
> 
> table: search_item
> 
> id_search    id_item
> --------------------------------------
> 1            1
> 1            2
> 1            3
> 2            2
> 2            3
> 3            1
> 3            3
> 
> 
> Now what I want to have is the items that match with id_search 1 and 2 
> and 3. Therefore I use the following SQL query.
> 
> SELECT * FROM item WHERE id IN
>     (SELECT id_item FROM search_item WHERE id_search=1 AND id_item IN
>         (SELECT id_item FROM search_item WHERE id_search=2 AND id_item IN
>             (SELECT id_item FROM search_item WHERE id_search=3)));
> 
> This should only return id_item 3. Would this be the best SQL query to 
> get this result? I have the feeling there should be something better, 
> but I cannot find it. Anyone has a hint?
> 



SELECT * FROM item WHERE id IN
     (SELECT id_item FROM search_item WHERE id_search=1
      INTERSECT
      SELECT id_item FROM search_item WHERE id_search=2
      INTERSECT
      SELECT id_item FROM search_item WHERE id_search=3);

Osvaldo

In response to

pgsql-sql by date

Next:From: W.Alphonse HAROUNYDate: 2007-09-13 12:34:32
Subject: Index usage in bitwise operation context
Previous:From: Alvaro HerreraDate: 2007-09-12 12:52:03
Subject: Re: Extracting hostname from URI column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group