homepage Welcome to WebmasterWorld Guest from 54.161.220.160
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Compare multiple rows in two tables
alexelisenko

5+ Year Member



 
Msg#: 4180956 posted 5:42 am on Aug 3, 2010 (gmt 0)

Hello,

I am stumped as to how to build this query, or if its even possible.

I has three tables, info, tagmap, tags

Table 1: Info

id----Title---
0 | Example
1 | Example2

Table 2: Tagmap

id---itemid----tagid---
0 | 0 | 1
1 | 0 | 2
3 | 1 | 3
4 | 1 | 4

Table 3: Tags

id----name----
0 | Tagone
1 | Tagtwo
3 | Tagthree
4 | Tagfour


So as you can see I have a tagmap table that references an info item and a tagid, then a tag table that has the name of the tag

in the tagmap, there are 20-30 entries for each info item

I need to make a query that will look at all the tags that are associated with an item and return results for other items that have the most tags in common.

If anyone has any idea on how to do this please let me know.

I have tried comparing just 5 or so tags, on at a time but it does not return results that are relevant enough, so I think I need to compare all tags and return the items that has the most in common.

Thank you.

 

redhatlab

5+ Year Member



 
Msg#: 4180956 posted 10:06 pm on Aug 11, 2010 (gmt 0)

Hi,

You can try a query that join all tables at once. Something like this:

SELECT

a.id, a.title,
b.id, b.itemid
b.tagid, c.id, c.name

FROM info a

LEFT JOIN tagmap b
ON a.id = b.itemid

LEFT JOIN tags c
ON c.id = b.tagid

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved