How to perform this Select?

Question

How to perform this Select?

Navigation

#1 by (1 votes)

1

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

Table_A:

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

Table_B

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

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

I'd prefer the query to return only the semanual values found in table_B, ignoring the others that were used only as parameters for the search.

I'm 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 the subtitles, it is my table_A from where I will get the last item that the user watched and then look at the table_B movies, their similar ones, however when using SUBSTRING_INDEX I am limited to having to type each time a separate index. Keeping in mind that the genres column is a string containing the comma separated categories, how would such a query be possible?

mysql select

asked by anonymous 15.07.2018 / 19:05

1 answer

How to Generate Jquery Load Bar Table of feeling ordering (Tidytext)

score 1 · Answer 1

I believe that using the MySQL function called FIND_IN_SET() you can achieve the result, since it will look for the genre, in tabela_c , within string_list , genres of 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)
";

FIND_IN_SET ()

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