CODEWITHSUNDEEP
mysqlsql
Andres SK
Andres SK

Reputation: 10974

Generating SQL query to get related results

I have a theme gallery system. I need to implement a 'related themes' item inside the theme preview page.

Each theme has colors and tags, so between 5 tables i have:

The catch, is that I only have to receive 5 results, and they must show the ones that have more matches between colors and tags. With that requirement, I have no idea on where to start. Any ideas?

Upvotes: 0

Views: 99

Answers (2)

Andrew
Andrew

Reputation: 4624

If you want the five themes that have the most items in common with a given theme, you might try something like:

DECLARE @target_id_theme INT;
SET @target_id_theme = 1; -- this is the id_theme you want to find similar themes for

SELECT t.id_theme, COUNT(*) as matching_things
FROM theme AS t
LEFT OUTER JOIN theme_color AS tc ON tc.id_theme = t.id_theme
LEFT OUTER JOIN theme_tag AS tt ON tt.id_theme = t.id_theme
WHERE tc.id_color IN (SELECT id_color FROM theme_color WHERE id_theme = @target_id_theme)
OR tt.id_tag IN (SELECT id_tag FROM theme_tag WHERE id_theme = @target_id_theme)
GROUP BY t.id_theme
ORDER BY COUNT(*) DESC
LIMIT 5

Not tested, buyer beware, but I hope you get the idea. This creates a row for every color or tag that matches a color or tag assigned to the @target_id_theme, orders them by count descending, and gives you the top 5.

Upvotes: 1

Matt Hudson
Matt Hudson

Reputation: 7348

Try this: 

SELECT * FROM theme t
   FULL OUTER JOIN theme_color tc ON tc.id_theme = t.id_theme
   INNER JOIN color c ON c.id_color = tc.id_color
   FULL OUTER JOIN theme_tag tt ON tt.id_theme = t.id_theme
   INNER JOIN tag ta ON ta.id_tag = tt.id_tag
WHERE tag LIKE '%' + @KEYWORD + '%' OR color LIKE '%' + @KEYWORD + '%' OR title LIKE '%' + @KEYWORD + '%'

Upvotes: 0

Related Questions