 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / February 2008Find similar items
I have a single table that contains information of photos ie. ID PhotoName PhotoTags 23 my cat cat animal pet 24 cell phone electronic communication 25 tiger animal zoo What would be a possible way to write a query that returns similar items - share similar tags, similar photo name can this be done with a sql query? Thank you Tem It can, but you need a better design. Each individual tag should be in a row by itself in a PhotoTags table, with a foreign key to the Photos table.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau I have a single table that contains information of photos ie. ID PhotoName PhotoTags 23 my cat cat animal pet 24 cell phone electronic communication 25 tiger animal zoo What would be a possible way to write a query that returns similar items - share similar tags, similar photo name can this be done with a sql query? Thank you Tem How would I write that query? Thanks > It can, but you need a better design. Each individual tag should be in a > row by itself in a PhotoTags table, with a foreign key to the Photos [quoted text clipped - 15 lines] > Thank you > Tem need to join the table on itself for this. note it will be SLOOOOWWWW if the table is huge. select t1.*, t2.* from yourtable t1 join yourtable t2 on t1.photoname = t2.photoname and t1.phototags = t2.phototags SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net >I have a single table that contains information of photos > [quoted text clipped - 11 lines] > Thank you > Tem thanks ill try it > need to join the table on itself for this. note it will be SLOOOOWWWW if > the table is huge. [quoted text clipped - 18 lines] >> Thank you >> Tem >> can this be done with an SQL query? << Yes, but why not buy a document or textbase package which is designed to work with this type of data? any recommendations? >>> can this be done with an SQL query? << > > Yes, but why not buy a document or textbase package which is designed > to work with this type of data? If you are on 2005, check Term Extraction and Term Lookup Transformations You can build pretty much "smart" service on top of this If you are interested in fuzzy matching - you can take a look at Fuzzy Lookup and Fuzzy Grouping Transformations SignatureMikhail Berlyant Senior Data Architect MySpace.com >I have a single table that contains information of photos > [quoted text clipped - 11 lines] > Thank you > Tem this is very cool! > If you are on 2005, check Term Extraction and Term Lookup Transformations > You can build pretty much "smart" service on top of this [quoted text clipped - 16 lines] >> Thank you >> Tem Table Tags ID TagName PhotoId 1 cat 23 2 animal 23 3 pet 23 4 animal 25 select PhotoId from Tags where PhotoId = 23 and (other photoId that has 23's tags) Need some help with this sql statement > I have a single table that contains information of photos > [quoted text clipped - 11 lines] > Thank you > Tem So is the requirement that there must be a match on ANY tags of PhotoId = 23 (in which case, PhotoID = 25 WILL match) or is it that you want a match on ALL tags of PhotoId = 23 (in which case PhotoId WILL NOT match)? SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Table Tags ID TagName PhotoId 1 cat 23 2 animal 23 3 pet 23 4 animal 25 select PhotoId from Tags where PhotoId = 23 and (other photoId that has 23's tags) Need some help with this sql statement "Tem" <tem1232@yahoo.com> wrote in message news:uUVgy74YIHA.484@TK2MSFTNGP06.phx.gbl... > I have a single table that contains information of photos > [quoted text clipped - 11 lines] > Thank you > Tem What im trying to say is since 23 has the tags "cat animal pet" it should return other photoIds that contain "cat OR animal OR pet" (in any order) 1 cat 23 2 animal 23 3 pet 23 4 animal 25 5 dog 25 If possible put the closest matching ones "cat AND animal AND pet" at the top. Hence getting similar photos > So is the requirement that there must be a match on ANY tags of PhotoId = > 23 [quoted text clipped - 31 lines] >> Thank you >> Tem Then this should do it: select t1.PhotoID , count (*) from Tags t1 join Tags t2 on t2.TagName = t1.TagName where t2.PhotoId = 23 and t1.PhotoId <> 23 group by t1.PhotoID order by count (*) desc SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau What im trying to say is since 23 has the tags "cat animal pet" it should return other photoIds that contain "cat OR animal OR pet" (in any order) 1 cat 23 2 animal 23 3 pet 23 4 animal 25 5 dog 25 If possible put the closest matching ones "cat AND animal AND pet" at the top. Hence getting similar photos > So is the requirement that there must be a match on ANY tags of PhotoId = > 23 [quoted text clipped - 29 lines] >> Thank you >> Tem The query did not return anything ID TagName PhotoID 1 cat 23 2 animal 23 3 pet 23 4 animal 25 5 dog 25 6 car 26 7 phone 26 if in the query @PhotoID = 25, it should return 23 if in the query @PhotoID = 26, it should return nothing > Then this should do it: > [quoted text clipped - 64 lines] >>> Thank you >>> Tem Please ignore my last post Count still does not work ID TagName PhotoID 1 cat 23 2 animal 23 3 pet 23 4 animal 25 5 dog 25 6 car 26 7 phone 26 8 cat 27 9 animal 27 if in the query @PhotoID = 25, it should return PhotoId Count 23 1 and if in the query @PhotoID = 27, it should return PhotoId Count 27 2 25 1 > The query did not return anything > [quoted text clipped - 80 lines] >>>> Thank you >>>> Tem My original code works. Here's a complete repro: create table Tags ( ID int primary key , TagName varchar (12) not null , PhotoID int not null ) go insert Tags values (1, 'cat', 23) insert Tags values (2, 'animal', 23) insert Tags values (3, 'pet', 23) insert Tags values (4, 'animal', 25) insert Tags values (5, 'dog', 25) insert Tags values (6, 'car', 26) insert Tags values (7, 'phone', 26) insert Tags values (8, 'cat ', 27) insert Tags values (9, 'animal', 27) go select t1.PhotoID , count (*) from Tags t1 join Tags t2 on t2.TagName = t1.TagName where t2.PhotoId = 27 and t1.PhotoId <> 27 group by t1.PhotoID order by count (*) desc go drop table tags SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Please ignore my last post Count still does not work ID TagName PhotoID 1 cat 23 2 animal 23 3 pet 23 4 animal 25 5 dog 25 6 car 26 7 phone 26 8 cat 27 9 animal 27 if in the query @PhotoID = 25, it should return PhotoId Count 23 1 and if in the query @PhotoID = 27, it should return PhotoId Count 27 2 25 1 "Tem" <tem1232@yahoo.com> wrote in message news:OSrbKbIZIHA.4448@TK2MSFTNGP03.phx.gbl... > The query did not return anything > [quoted text clipped - 69 lines] >>>> Thank you >>>> Tem thanks > My original code works. Here's a complete repro: > [quoted text clipped - 133 lines] >>>>> Thank you >>>>> Tem Below is VERY SIMPLIFIED example : you create dictionary table and populate it with distinct terms from all phrases you have: TagID TagName 1 cat 2 animal 3 pat 4 electronic 5 communication 6 zoo Next you join this Dictionary table with Phrase table and end up with Tags table (in more real example you would have here frequency field, but for now you can skip this for simplicity sake): PhotoID TagID 23 1 23 2 23 3 24 4 24 5 25 2 25 6 Your final query would look like: SELECT PhotoID, COUNT(*) AS Score FROM Tags WHERE TagID in (SELECT TagID FROM Tags WHERE PhotoID = 23) GROUP BY PhotoID ORDER BY Score DESC I hope you will get an idea SignatureMikhail Berlyant Senior Data Architect MySpace.com > Table Tags > ID TagName PhotoId [quoted text clipped - 23 lines] >> Thank you >> Tem So with this design I would have 3 tables? Photos Tags TagFreq is that right? > Below is VERY SIMPLIFIED example : > you create dictionary table and populate it with distinct terms from all [quoted text clipped - 58 lines] >>> Thank you >>> Tem So far, these are only I see you need SignatureMikhail Berlyant Senior Data Architect MySpace.com > So with this design I would have 3 tables? > [quoted text clipped - 65 lines] >>>> Thank you >>>> Tem Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.