Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 8A7CE9F99E0
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Sep 2007 11:16:01 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 42627-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Sep 2007 11:15:59 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from smtp108.mail.mud.yahoo.com (smtp108.mail.mud.yahoo.com
	[209.191.85.218])
	by postgresql.org (Postfix) with SMTP id 85FE09F97AF
	for <pgsql-sql@postgresql.org>; Wed, 12 Sep 2007 11:15:58 -0300 (ADT)
Received: (qmail 56633 invoked from network); 12 Sep 2007 14:15:53 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Received:X-YMail-OSG:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
	b=drAApHGD7GqdJg3A0zOr928lgR/GJ/SJTMGvewu0Dy0w78i33O64TvqixBjm/rS+8gFvp0PVqdtgMsNiXqQ3JIYWUQfXtWrGt1V+eNIq4W54sb+C+b9IGYeo1rpDsF85XTutOCtVbregjhJ79lzcVleWG434E87CXa+t5c7B5+Q=
	;
Received: from unknown (HELO ?10.2.200.151?) (osvaldo_kussama@189.25.55.205
	with plain)
	by smtp108.mail.mud.yahoo.com with SMTP; 12 Sep 2007 14:15:52 -0000
X-YMail-OSG: 
 7syFl_cVM1ktyxXyXhB8IWG34rnf8tr7T4xQ352mqB5JPvCHa_frg6_zjiBpJ9o5RRV9ABV4Tg--
Message-ID: <46E7F614.8070201@yahoo.com.br>
Date: Wed, 12 Sep 2007 11:22:12 -0300
From: Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
To: Koen Bok <koen@madebysofa.com>,  pgsql-sql@postgresql.org
Subject: Re: Simple Query?
References: <2C0EA394-5AC8-4FC8-99F3-5FAEE9720360@madebysofa.com>
In-Reply-To: <2C0EA394-5AC8-4FC8-99F3-5FAEE9720360@madebysofa.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.105 tagged_above=0 required=5 tests=AWL=0.104,
	BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 200709/100
X-Sequence-Number: 29313

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