From: Dirk Griffioen <dirk@code-shop.com>
To: pgsql-sql@postgresql.org
Subject: join and sort on 'best match'
Date: Wed, 13 Dec 2006 10:26:41 +0100
Message-ID: <457FC751.6080104@code-shop.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dirk@code-shop.com
Subject: Re: join and sort on 'best match'
In-Reply-To: <457FC751.6080104@code-shop.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox