How to perform this Select?

Asked

Viewed 59 times

1

I need to relate data according to the last item found in the user table, for example:

Table:

+----------------+-------------+
| user_id        | title_id    |
+----------------+-------------+
| 154138746      | 123         |
+----------------+-------------+

Table

+----------------+----------------------+
| title_id       | genres               |
+----------------+----------------------+
| 123            | Ação, Aventura, Drama|
+----------------+----------------------+
| 122            | Drama, Sci-fi        |
+----------------+----------------------+
| 126            | Romance, Aventura    |
+----------------+----------------------+
| 135            | Ação , Drama         |
+----------------+----------------------+

What I need is to make a SELECT in table A by recovering its respective data in table B, and then using such data recover from the same table B its similar based on category, it is possible to do everything in a query or it is better to split?

Preferably I would like the query to return only the semantic values found in the tabela_B, ignoring the others that were used only as parameters for the query.

I am trying with the following query:

SELECT  wt.user_id, wt.title_id, 
        fl.id, fl.genres, 
        sm.* 
            FROM watchedtitles wt 
            INNER JOIN filmes fl 
                ON wt.title_id = fl.id 

            LEFT JOIN filmes sm 
                ON FIND_IN_SET(SUBSTRING_INDEX(fl.genres, ',', 1), fl.genres)

In the case of watchedtitles it is my table_A from where I will pick the last item that the user watched and then search in the table_B movies, their similar ones, but when using SUBSTRING_INDEX I am limited to having to every time type separate index. Bearing in mind that the genres column is a string containing the categories separated by comma, how could such a query?

mysql select

  • Instead of column types you could put an example of value?

  • @Guilhermecostamilam added some values to the tables

  • 1

    Try with REGEXP CONCAT('(', REPLACE(fl.genres, ', ', '|'), ')'), this should create a regex of the type (Ação|Aventura|Drama), you may have to make some adaptations

  • Could you give me an example of what such an adaptation would look like?

  • 1

    I can’t test sql now, but the idea is to transform the column value genres in a regex that can be used to look for similar movies, it would look something like LEFT JOIN filmes sm ON sm.genres REGEXP CONCAT('(', REPLACE(fl.genres, ', ', '|'), ')'), but like I said I can’t test to make sure it works

  • It seems to work well, it would be possible to make an ORDER BY by the results containing more common categories?

  • I can’t think of any simple way to do this, but to get around the problem you can let the user define their favorite categories and sort by them

Show 2 more comments

1 answer

0

I believe using the function MySQL calling for FIND_IN_SET() you can achieve the result, once it will fetch the gender, in the tabela_c, within a string_list, genres of the tabela_b:

$sql="
    SELECT
        tabela_a.*,
        tabela_b.*,
        tabela_c.*
    FROM tabela_a
    INNER JOIN tabela_b
    ON tabela_a.title_id=tabela_b.title_id
    INNER JOIN tabela_c
    WHERE FIND_IN_SET(tabela_c.genres,tabela_b.genres)
";

Reference FIND_IN_SET()

  • OBS: The JOINS and table fields, *, you can change according to your need, it was just exemplified.

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.