declare @books TABLE(book_id int, descr nvarchar(50))
insert into @books 
SELECT 1, 'Gone with the wind'
UNION ALL
SELECT 2, 'Mars'
UNION ALL
SELECT 3, 'Do Androids Dream of Electric Sheep?'
UNION ALL
SELECT 4, 'Ender''s Game'
UNION ALL
SELECT 5, 'Dune'
UNION ALL
SELECT 6, 'Foundation'
UNION ALL
SELECT 7, 'Hitch Hiker''s Guide to the Galaxy'
UNION ALL
SELECT 8, '1984'
UNION ALL
SELECT 9, 'Star Wars'
UNION ALL
SELECT 10, 'Battlestar Galactica'
UNION ALL
SELECT 11, 'Fringe'
UNION ALL
SELECT 13, 'Prometheus'
UNION ALL
SELECT 14, 'Prometheus VI'
UNION ALL
SELECT 15, 'Contagion'
UNION ALL
SELECT 16, 'Deep Blue'
UNION ALL
SELECT 16, 'Hunger Games'

--SELECT * FROM @books
declare @terminologies TABLE(terminology_id int, descr nvarchar(50))
INSERT INTO @terminologies 
SELECT 1, 'Futuristic'
UNION ALL
SELECT 2, 'Propulsion'
UNION ALL
SELECT 3, 'Guidance'
UNION ALL
SELECT 4, 'Life Support'
UNION ALL
SELECT 5, 'Cabin Structure'
UNION ALL
SELECT 6, 'Communications'
UNION ALL
SELECT 7, 'Thermal Protection'
UNION ALL
SELECT 8, 'Displays And Controls'
UNION ALL
SELECT 9, 'Space Craft'
UNION ALL
SELECT 10, 'Light Speed'
UNION ALL
SELECT 11, 'Warp Speed'
UNION ALL
SELECT 12, 'Jedi'
UNION ALL
SELECT 13, 'Force is weak with you rookie Jedi'
UNION ALL
SELECT 14, 'UnCivil War'

--SELECT * FROM @terminologies

DECLARE @book_terminologies TABLE(book_id int, terminology_id int)

INSERT INTO  @book_terminologies
        ( book_id, terminology_id )
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
UNION ALL
SELECT 1, 5
UNION ALL
SELECT 1, 6
UNION ALL
SELECT 1, 7
UNION ALL
SELECT 1, 8
UNION ALL
SELECT 1, 9
UNION ALL
SELECT 1, 10
UNION ALL


SELECT 2, 1
UNION ALL
SELECT 2, 2
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 2, 4
UNION ALL
SELECT 2, 5
UNION ALL
SELECT 2, 6
UNION ALL
SELECT 2, 7
UNION ALL
SELECT 2, 8
UNION ALL
SELECT 2, 9
UNION ALL
SELECT 2, 10
UNION ALL
SELECT 3, 1
UNION ALL
SELECT 3, 2
UNION ALL
SELECT 3, 3
UNION ALL
SELECT 3, 4
UNION ALL
SELECT 3, 5


SELECT b.descr, b.book_id,
       t.descr, t.terminology_id
  FROM @books b
  
  inner join @book_terminologies bt
  on b.book_id = bt.book_id
  
  inner join @terminologies t
  on t.terminology_id = bt.terminology_id
order by b.book_id, t.terminology_id

SELECT 
b1.book_id, b2.book_id, COUNT(*)
  FROM @books b1 
  inner join @book_terminologies t1
  on b1.book_id = t1.book_id
  inner join @book_terminologies t2
  on t1.terminology_id = t2.terminology_id
  inner join @books b2
  on b2.book_id = t2.book_id
where b1.book_id<>b2.book_id  
group by b1.book_id,b2.book_id
having COUNT(*)>=5

SELECT 
b1.book_id, b2.book_id, COUNT(*)
  FROM @books b1 
  inner join (select distinct * from @book_terminologies) t1
  on b1.book_id = t1.book_id
  inner join (select distinct * from @book_terminologies) t2
  on t1.terminology_id = t2.terminology_id
  inner join @books b2
  on b2.book_id = t2.book_id
where b1.book_id<>b2.book_id  
group by b1.book_id,b2.book_id
having COUNT(*)>=5

quote:

---

Originally posted by Bustaz Kool

[CODE];with BooksWithSameTerminology
as (
select bt1.book_id, bt1.terminology_id, bt2.book_id book_id2
from @book_terminologies bt1
inner join
@book_terminologies bt2
on bt1.book_id < bt2.book_id
and bt1.terminology_id = bt2.terminology_id
),
BooksWithFiveMatches
as (
select book_id, book_id2, count(*) kount
from BooksWithSameTerminology
group by book_id, book_id2
having count(*) >= 5
)
select
b1.descr, b2.descr, bwfm.kount
from
@books b1
inner join
BooksWithFiveMatches bwfm
on bwfm.book_id = b1.book_id
inner join
@books b2
on bwfm.book_id2 = b2.book_id[/CODE]HTH

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

---

quote:

---

I assume you mean to say <> instead of>

---