Find similar items

[Tem]

thanks

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