Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Transfer-Encoding: 7bit
Message-Id: <2C0EA394-5AC8-4FC8-99F3-5FAEE9720360@madebysofa.com>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
To: pgsql-sql@postgresql.org
From: Koen Bok <koen@madebysofa.com>
Subject: Simple Query?
Date: Tue, 11 Sep 2007 23:27:19 +0200

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?