1

With this relevance query i am trying to select/find a tag tags.tag 'computational thinking' by querying a part 'computational' from articles.id 16, however it doesnt seem to find more than one tag. Once i search for 'almere' it does give the desired article result (article.id 16 and 15)

SELECT s.seoUrl, a.*,
        (
            (
            if (t.tag LIKE '%computational%',7,0)
            )+
            (
            if (a.title LIKE '%computational%',5,0)
            )+
            (
            if (a.shortDescription LIKE '%computational%',4,0) 
            )+
            (
            if (a.content LIKE '%computational%',3,0)
            )+
            (
            if (m.name LIKE '%computational%',2,0)
            )+
            (
            if (s.seoUrl LIKE '%computational%',1,0)
            )
        ) as relevance
        FROM articles a
        LEFT JOIN article_tag at ON at.articleId = a.contentId
        LEFT JOIN seoUrls s ON s.contentId = a.contentId
        LEFT JOIN tags t ON at.tagId = t.contentId
        LEFT JOIN article_category ac ON ac.articleId = a.contentId
        LEFT JOIN menu m ON m.contentId = ac.categoryParentId
        WHERE a.published = 'on'
        GROUP BY a.contentId
        HAVING relevance > 0
        ORDER BY relevance DESC, a.views DESC

i think i made a massive mistake with the left joins within the selection of tags but i can't seem to find a solution, any ideas?

Fiddle: http://sqlfiddle.com/#!9/7a9d0f9/1

Darkshifty
  • 343
  • 5
  • 12
  • 1
    `GROUP BY` does not match `SELECT`. Maybe adding `MAX` will help **[DBFiddle Demo](http://sqlfiddle.com/#!9/7a9d0f9/11/0)** – Lukasz Szozda Jul 14 '18 at 12:21
  • 1
    Why are GROUP BY and HAVING clauses there? you need to use DISTINCT to remove duplicates.. http://sqlfiddle.com/#!9/7a9d0f9/14 .. Most likely you want to do this http://sqlfiddle.com/#!9/7a9d0f9/16 – Raymond Nijland Jul 14 '18 at 12:23
  • 2
    You don't have any aggregate functions here so the group by is irrelevant and probably at fault ,remove it and you get 1 record returned. – P.Salmon Jul 14 '18 at 12:31
  • I see, thank you. I indeed used the group by to avoid duplicates. However the `DISTINCT` fails once a record has multiple 'seoUrls' http://sqlfiddle.com/#!9/00b57f/1 and doesnt select `DISTINCT` 'contentId' if you have multiple queries http://sqlfiddle.com/#!9/00b57f/2 and the `MAX` fails on multiple queries too http://sqlfiddle.com/#!9/7a9d0f9/21 – Darkshifty Jul 14 '18 at 16:26
  • i noticed i had some errors, the MAX solution provided me what i needed: http://sqlfiddle.com/#!9/7a9d0f9/30 thank you! – Darkshifty Jul 17 '18 at 14:52

0 Answers0