Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E71239FA433
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 13 Dec 2006 05:26:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 07749-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 13 Dec 2006 05:26:47 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 193DB9F9F46
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 05:26:47 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from mail.code-shop.com (lvps87-230-13-34.dedicated.hosteurope.de
	[87.230.13.34])
	by svr4.postgresql.org (Postfix) with ESMTP id 40B9E5AF67D
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 09:26:41 +0000 (GMT)
Received: from localhost (lvps87-230-13-34.dedicated.hosteurope.de
 [127.0.0.1])
	by mail.code-shop.com (Postfix) with ESMTP id AF0222442058
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 10:26:38 +0100 (CET)
Received: from mail.code-shop.com ([127.0.0.1])
	by localhost (lvps87-230-13-34.dedicated.hosteurope.de [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id XVv1LJPquVif for <pgsql-sql@postgresql.org>;
	Wed, 13 Dec 2006 10:26:38 +0100 (CET)
Received: from [20.20.20.13] (195-241-134-87-static.dsl.ip.tiscali.nl
	[195.241.134.87])
	by mail.code-shop.com (Postfix) with ESMTP id 3340F2442057
	for <pgsql-sql@postgresql.org>; Wed, 13 Dec 2006 10:26:38 +0100 (CET)
Message-ID: <457FC751.6080104@code-shop.com>
Date: Wed, 13 Dec 2006 10:26:41 +0100
From: Dirk Griffioen <dirk@code-shop.com>
Organization: CodeShop
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: join and sort on 'best match'
Content-Type: multipart/alternative;
	boundary="------------030601010208030905020602"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/100
X-Sequence-Number: 27056

This is a multi-part message in MIME format.
--------------030601010208030905020602
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

  Hi Everybody,

I have been breaking my head on the following problem: how to join 2 
tables and sort the results on the best match.

explanation:

- there are 3 tables, items, tags and items_tags. The items_tags table 
links items to tags.
- I have one item which has certain tags, and I want to look up all the 
other items that have those tags as well
- results should be sorted and presented by 'best match': first all the 
items that have 3 tags in common, then 2 and last 1

example:

Item 1 : news, nature, greenpeace
Item 2 : news, nature
Item 3 : news, nature, greenpeace, whale

Item 1 and Item 3 are the best match.

So far, the SQL I came up wiht looks like:

SELECT id, COUNT(items_tags.item_id) AS quantity
FROM items JOIN items_tags ON items_tags.item_id = items.id
WHERE id in (select item_id from items_tags where tag_id in (select 
tag_id from items_tags where item_id=?))
GROUP BY items_tags.item_id,id
ORDER BY quantity DESC

note: the '?' in the query represents the dynamic part: I have 1 item 
and I want to look up matching items.

To me, this query means the following:

- get all items that have tags, the 'JOIN', and count the tags, but only 
those that match on the same tags, the 'WHERE'
- then show them

I thought I had found the solution (my test cases worked), but I now 
find cases that should be found by the query but are not.

Can anyone please help me?

Dirk



--------------030601010208030905020602
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-html" lang="x-unicode"> <font
 face="Helvetica, Arial, sans-serif">Hi Everybody,<br>
<br>
I have been breaking my head on the following problem: how to join 2
tables and sort the results on the best match.<br>
<br>
explanation:<br>
<br>
- there are 3 tables, items, tags and items_tags. The items_tags table
links items to tags.<br>
- I have one item which has certain tags, and I want to look up all the
other items that have those tags as well<br>
- results should be sorted and presented by 'best match': first all the
items that have 3 tags in common, then 2 and last 1<br>
<br>
example:<br>
<br>
<font face="Courier New, Courier, monospace">Item 1 : news, nature,
greenpeace
<br>
Item 2 : news, nature
<br>
Item 3 : news, nature, greenpeace, whale</font><br>
<br>
Item 1 and Item 3 are the best match.
<br>
<br>
So far, the SQL I came up wiht looks like:<br>
<br>
<small><font face="Courier New, Courier, monospace">SELECT id,
COUNT(items_tags.item_id) AS quantity <br>
FROM items JOIN items_tags ON items_tags.item_id = items.id <br>
WHERE id in (select item_id from items_tags where tag_id in (select
tag_id from items_tags where item_id=?)) <br>
GROUP BY items_tags.item_id,id <br>
ORDER BY quantity DESC </font></small><br>
<br>
note: the '?' in the query represents the dynamic part: I have 1 item
and I want to look up matching items.<br>
<br>
To me, this query means the following:<br>
<br>
- get all items that have tags, the 'JOIN', and count the tags, but
only those that match on the same tags, the 'WHERE'<br>
- then show them <br>
<br>
I thought I had found the solution (my test cases worked), but I now
find cases that should be found by the query but are not.<br>
<br>
Can anyone please help me?<br>
<br>
Dirk<br>
<br>
<br>
</font>
</div>
</body>
</html>

--------------030601010208030905020602--