Hello,

I am facing some difficulties with a complex SQL query and I wonder how to translate it using LLBL predicates & entities.

Below is an explanation of what I want:

Tables structure - Article (pk ART_I_ID) - Category (pk CAT_I_ID) Each article can have several categories. Therefore I am having the following structure - Article (pk ART_I_ID) - ArticleCategory (pk ART_I_ID,pk CAT_I_ID) - Category (pk CAT_I_ID)

I would like to retrieve for 1 particular article, all the articles that have at least 3 categories in common with my article.

How can I do that with LLBL?

Below is my SQL query:

--Keep only the first one.
Select Top 10 ART_I_ID From 
   (-- Count the number of Categories that are common 
 Select count(ART_I_ID) nb , ART_I_ID  From 
     ( 
  -- Get all articles that have at least a category in common with my article (@ART_I_ID)
       Select Top 1000 ArticleCategory .*
       From ArticleCategory  (nolock)
   Join t_articles (nolock) On ArticleCategory.ART_I_ID = t_articles.ART_I_ID 
       Where 
         CAT_I_ID In (  
      --Get all the CAT_I_ID of an article (@ART_I_ID) 
                       Select ArticleCategory.CAT_I_ID 
                       From ArticleCategory  (nolock)
        join category (nolock) On ArticleCategory .CAT_I_ID = category.CAT_I_ID 
                       Where   ArticleCategory.ART_I_ID = @ART_I_ID
                   ) 
         and ArticleCategory .ART_I_ID <> @ART_I_ID
       Order By ArticleCategory .ART_I_ID Desc
     ) tmp1 
    Group By ART_I_ID 
   ) tmp2 
-- We only keep the articles that have at least 4 categories in common
Where compte > 3
order by ART_I_ID DESC

For this kind of complexity, a database view or a stored procedure is the best option.

So either change your Query to fit in a view then you should filter it with the ArticleID. Or use a SP & and pass the ArticleID as a paameter for minimum hassles.