Find similar items

[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

 

[Tom Moreau]

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.

--
Tom

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

 

[TheSQLGuru]

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

 

[--CELKO--]

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?

 

[Tem]

How would I write that query?

Thanks

 

[Tem]

any recommendations?

Yes, but why not buy a document or textbase package which is designed
to work with this type of data?

 

[Tem]

thanks ill try it

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

 

[Mikhail Berlyant]

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

 

[ProgTalk]

I'll give a simple solution. Lets say you have the fields ID, Name, Tags in Table1.
Just do this query:

select * from Table1 where (Tags like '%cat%') or (Tags like '%cell%') or (Tags like '%dog%')

It will return the full set.

 

[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
If you are interested in fuzzy matching - you can take a look at Fuzzy
Lookup and Fuzzy Grouping Transformations

 

[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

 

[Tom Moreau]

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


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

 

[Mikhail Berlyant]

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

 

[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

 

[Tem]

So with this design I would have 3 tables?

Photos Tags TagFreq

is that right?

 

[Mikhail Berlyant]

So far, these are only I see you need

 

[Tom Moreau]

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

--
Tom

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

 

[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

 

[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

 

[Tom Moreau]

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

--
Tom

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