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