Thread: join and sort on 'best match'

<div class="moz-text-html" lang="x-unicode"><font face="Helvetica, Arial, sans-serif">Hi Everybody,<br /><br /> I have
beenbreaking 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
last1<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
thebest 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_tagswhere 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
thesolution (my test cases worked), but I now find cases that should be found by the query but are not.<br /><br /> Can
anyoneplease help me?<br /><br /> Dirk<br /><br /><br /></font></div> 

On mið, 2006-12-13 at 10:26 +0100, Dirk Griffioen wrote:

> I have been breaking my head on the following problem: how to join 2
> tables and sort the results on the best match. ^^^^^^^^^^^^^
> - 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

looks to me like you want to join:
items->item_tags->tags->item_tags->items

so the basic select is:
SELECT * FROM      items AS i1      JOIN items_tags AS it1 ON (it1.item_id = i1.id)      JOIN tags AS t ON (t.tag_id =
it1.tag_id)     JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id)      JOIN items AS i2 ON (i2.id = it2.item_id) WHERE
i1.id=?

> - results should be sorted and presented by 'best match': first all
> the items that have 3 tags in common, then 2 and last 1

this would be: 

SELECT i1.id,i2.id,COUNT(*) as quantity FROM      items AS i1      JOIN items_tags AS it1 ON (it1.item_id = i1.id)
JOINtags AS t ON (t.tag_id = it1.tag_id)      JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id)      JOIN items AS i2
ON(i2.id = it2.item_id) WHERE i1.id=? GROUP by i1.id,i2.id ORDER BY quantity DESC
 

> 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.

if this does not work, please provide us with a counter example.

gnari

Ashish

Hi, Ashish,

"Ashish Ahlawat" <ahlawat.ashish@gmail.com> wrote:

> hi pls tell me ----
> 
> if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need
> join ??*

Please try to write in English, so we can understand and answer your
questions.



Regards,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

On fös, 2006-12-15 at 22:10 +0530, Ashish Ahlawat wrote:
>  
>  
> Hi team
>  
> I was just asking that If table ITEM3 has all the columns then why we
> need to have a join ?? even we put a sorting very easily.

> On 12/14/06, Markus Schaber <schabi@logix-tt.com> wrote: 
>         Hi, Ashish,
>         
>         "Ashish Ahlawat" <ahlawat.ashish@gmail.com> wrote:
>         
>         > if table *Item 3 : news, nature, greenpeace, whale has all
>         clmn y v need
>         > join ??*

it seems these where not columns of a table.

the organisation was like this:

table items:

id   name
-----------
1    foo
2    bar
3    item3


table tags:

tag_id   text
---------------------
1        news
2        nature
3        tennisballs
4        greenpeace
5        cannibals
6        whale

and table items_tags:

item_id  tag_id
----------------
3        1
3        2
3        4
3        6
...


so table items needs to be joined to table tags via 
table items_tags to retrieve the connections from item3
to news, nature, greepeace and whales.

gnari