By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,313 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,313 IT Pros & Developers. It's quick & easy.

Find similar items

P: n/a
Tem
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

Jan 30 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tem" <te*****@yahoo.comwrote in message
news:uU*************@TK2MSFTNGP06.phx.gbl...
>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

Jan 30 '08 #2

P: n/a
Tem
thanks ill try it

"TheSQLGuru" <kg*****@earthlink.netwrote in message
news:13*************@corp.supernews.com...
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
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tem" <te*****@yahoo.comwrote in message
news:uU*************@TK2MSFTNGP06.phx.gbl...
>>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

Jan 30 '08 #3

P: n/a
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

--
Mikhail Berlyant
Senior Data Architect
MySpace.com

"Tem" <te*****@yahoo.comwrote in message
news:uU*************@TK2MSFTNGP06.phx.gbl...
>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

Jan 31 '08 #4

P: n/a
Tem
this is very cool!

"Mikhail Berlyant" <tn*******@ecapsym.mocwrote in message
news:OK**************@TK2MSFTNGP05.phx.gbl...
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

--
Mikhail Berlyant
Senior Data Architect
MySpace.com

"Tem" <te*****@yahoo.comwrote in message
news:uU*************@TK2MSFTNGP06.phx.gbl...
>>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

Jan 31 '08 #5

P: n/a
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

"Tem" <te*****@yahoo.comwrote in message
news:uU*************@TK2MSFTNGP06.phx.gbl...
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
Jan 31 '08 #6

P: n/a
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)?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tem" <te*****@yahoo.comwrote in message
news:ua**************@TK2MSFTNGP02.phx.gbl...
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" <te*****@yahoo.comwrote in message
news:uU*************@TK2MSFTNGP06.phx.gbl...
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
Jan 31 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.