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.
--
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"
news:uUVgy74YIHA.484@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
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"
news:uUVgy74YIHA.484@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
>
>> 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?
How would I write that query?
Thanks
"Tom Moreau"
news:e4ur0$4YIHA.1204@TK2MSFTNGP03.phx.gbl...
> 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
>
>
> "Tem"
> news:uUVgy74YIHA.484@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
>
>
any recommendations?
"--CELKO--"
news:aed4d570-d9ac-4890-9cf7-e3634f216d70@s12g2000prg.google groups.com...
>>> 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?
thanks ill try it
"TheSQLGuru"
news:13q1untmhbnam3a@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"
> news:uUVgy74YIHA.484@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
>>
>
>
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"
news:uUVgy74YIHA.484@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
>
this is very cool!
"Mikhail Berlyant"
news:OK5Wrm6YIHA.1208@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"
> news:uUVgy74YIHA.484@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
>>
>
>
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"
news:uUVgy74YIHA.484@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
>
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"
news:uaC8ta9YIHA.5900@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"
news:uUVgy74YIHA.484@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
>
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
--
Mikhail Berlyant
Senior Data Architect
MySpace.com
"Tem"
news:uaC8ta9YIHA.5900@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"
> news:uUVgy74YIHA.484@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
>>
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
"Tom Moreau"
news:ORBGKsAZIHA.1204@TK2MSFTNGP03.phx.gbl...
> 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"
> news:uaC8ta9YIHA.5900@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"
> news:uUVgy74YIHA.484@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
>>
>
So with this design I would have 3 tables?
Photos Tags TagFreq
is that right?
"Mikhail Berlyant"
news:#v7s4jCZIHA.4284@TK2MSFTNGP03.phx.gbl...
> 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
>
> --
> Mikhail Berlyant
> Senior Data Architect
> MySpace.com
>
> "Tem"
> news:uaC8ta9YIHA.5900@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"
>> news:uUVgy74YIHA.484@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
>>>
>
>
So far, these are only I see you need
--
Mikhail Berlyant
Senior Data Architect
MySpace.com
"Tem"
news:eIFlRYFZIHA.4172@TK2MSFTNGP02.phx.gbl...
> So with this design I would have 3 tables?
>
> Photos Tags TagFreq
>
> is that right?
>
> "Mikhail Berlyant"
> news:#v7s4jCZIHA.4284@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>> --
>> Mikhail Berlyant
>> Senior Data Architect
>> MySpace.com
>>
>> "Tem"
>> news:uaC8ta9YIHA.5900@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"
>>> news:uUVgy74YIHA.484@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
>>>>
>>
>>
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
"Tem"
news:OBhmhXFZIHA.4860@TK2MSFTNGP03.phx.gbl...
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
"Tom Moreau"
news:ORBGKsAZIHA.1204@TK2MSFTNGP03.phx.gbl...
> 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"
> news:uaC8ta9YIHA.5900@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"
> news:uUVgy74YIHA.484@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
>>
>
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
"Tom Moreau"
news:uwDG#nFZIHA.4448@TK2MSFTNGP03.phx.gbl...
> 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
>
>
> "Tem"
> news:OBhmhXFZIHA.4860@TK2MSFTNGP03.phx.gbl...
> 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
>
> "Tom Moreau"
> news:ORBGKsAZIHA.1204@TK2MSFTNGP03.phx.gbl...
>> 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"
>> news:uaC8ta9YIHA.5900@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"
>> news:uUVgy74YIHA.484@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
>>>
>>
>
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"
news:OSrbKbIZIHA.4448@TK2MSFTNGP03.phx.gbl...
> 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
>
>
>
>
> "Tom Moreau"
> news:uwDG#nFZIHA.4448@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>>
>> "Tem"
>> news:OBhmhXFZIHA.4860@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>> "Tom Moreau"
>> news:ORBGKsAZIHA.1204@TK2MSFTNGP03.phx.gbl...
>>> 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"
>>> news:uaC8ta9YIHA.5900@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"
>>> news:uUVgy74YIHA.484@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
>>>>
>>>
>>
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
"Tem"
news:OqwS8vIZIHA.3652@TK2MSFTNGP02.phx.gbl...
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"
news:OSrbKbIZIHA.4448@TK2MSFTNGP03.phx.gbl...
> 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
>
>
>
>
> "Tom Moreau"
> news:uwDG#nFZIHA.4448@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>>
>> "Tem"
>> news:OBhmhXFZIHA.4860@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>> "Tom Moreau"
>> news:ORBGKsAZIHA.1204@TK2MSFTNGP03.phx.gbl...
>>> 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"
>>> news:uaC8ta9YIHA.5900@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"
>>> news:uUVgy74YIHA.484@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
>>>>
>>>
>>