Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Question Unanswered: Search multiple tags

    I tried the search function, but didn't find what I was looking for. Mainly because I'm quite unsure of what to really search for.

    I have a database, and I want to be able to search for tags associated with the movies I have in my database.

    Currently I have;
    - one table that contains the movies (id and title)
    - another table that contains the tags (id and title)
    - and finally I have a third table that contains the connections between the movies and the tags, the fields are as follows...

    id
    movie_id - the same 'id' as the 'id' in the movie table
    tag_id - the same 'id' as the 'id' in the tags table

    I want to be able to search multiple tags in the tag table and the results will show the related movies from the movie table.

    Anyone that could point me in the right direction of how to do this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select movie_id
      from movietags
     where tag_id in ( 1, 9, 3, 7 )
    group
        by movie_id
    having count(*) = 4
    basically, select the relationship rows for all the tags you're interested in, group by the movie, and keep only those movies which have the full number of tags
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    7
    Thanks for your reply.

    I not sure if this i the right way to do this... this is more of pseudo code of what I want to achieve...

    Code:
    SELECT title, id
      FROM tags t
      WHERE title LIKE 'string1%' OR title LIKE 'string2%'
    IN (
      SELECT *
       FROM movie_tags
       WHERE tag_id
        IN ( t.id, t.id, t.id )
        GROUP
        BY movie_id
    )
    I basically want to wild card match multiple strings found in the tags table, and get the movies related to the tags... is this the wrong approach?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it looks like you are wild card matching strings found in the movie title instead of the tags

    could you perhaps rephrase what you're trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2008
    Posts
    7
    Ok, I'll try to rephrase.

    So lets say I have 5 tags in the tags table.
    Code:
    id, title
    1, ball
    2, car
    3, cat
    4, dog
    5, doll
    and in the movies table I have 2 movies.
    Code:
    id, title
    1, movie about a dog
    2, movie about a cat
    in the movie_tags table I connect the tags to different movies...
    Code:
    id, movie_id, tag_id
    1, 1, 4
    2, 1, 3
    3, 1, 5
    4, 2, 3
    5, 2, 2
    6, 2, 1
    When I begin to search for "ca" it should wild card match all the movies tagged with car and cat.

    Do you understand what I'm trying to do?

  6. #6
    Join Date
    Apr 2008
    Posts
    7
    To maybe simplify what I'm trying to achieve, I split the SQL query in different parts. I practically just need to merge it somehow.

    Code:
    SELECT title, id FROM tags t
    WHERE title LIKE 's%'
    This will get all the tags starting with the letter 's'. Now I would like to use the id associated with the tag...

    Code:
    SELECT * FROM movie_tags
    WHERE tag_id IN
    (SELECT id FROM movie
    WHERE id IN ('7', '3'))
    This will get all movies tagged with id 7 and 3 (the id is linked to the id in the tags table). So the 7 and 3 have to be changed to the id's I got from the previous query.

    I think this is what I need, so how do I merge this into one query?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT movies.*
      FROM movies
    INNER
      JOIN ( SELECT movietags.movie_id
               FROM tags
             INNER
               JOIN movietags
                 ON movietags.tag_id = tags.id
              WHERE tags.title LIKE 'string1%' 
                 OR tags.title LIKE 'string2%'
             GROUP
                 BY movietags.movie_id
             HAVING COUNT(*) = 
                    ( SELECT COUNT(*)
                        FROM tags
                       WHERE title LIKE 'string1%' 
                          OR title LIKE 'string2%' )
           ) as t
        ON t.movie_id = movies.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by gewk
    Code:
    id, movie_id, tag_id
    1, 1, 4
    2, 1, 3
    3, 1, 5
    4, 2, 3
    5, 2, 2
    6, 2, 1
    A bit off topic but still...

    You don't need the "id" field here; a compound key of movie_id and tag_id is more than sufficient
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    more than????

    i'd like to see whatever you think might be sufficient that includes less than a compound key of movie_id and tag_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    True enough, should have have said with more conviction!

    I was going to joke about how you could use a comma delimited value string or a table with multiple tag fields, but I won't. The correct design is the compound key and that's that






    ...better?
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2008
    Posts
    7
    Oh, thank you guys! It's working like intended

  12. #12
    Join Date
    Apr 2008
    Posts
    7
    Sorry for bothering again...

    I've been trying to use the same string input for searching for the 'title' field in the movies table as well. But I haven't managed to get it to work.

    Something like...
    Code:
             SELECT movietags.movie_id
               FROM movies, tags
             INNER
               JOIN movietags
                 ON movietags.tag_id = tags.id
             WHERE tags.title LIKE 'string1%' 
                 OR tags.title LIKE 'string2%'
                 OR movies.title LIKE 'string1%'
                 OR movies.title LIKE 'string2%'
    Seems like the movies table and/or fields aren't recognized in the the sub query...

    Anyone care to help me out?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no subquery

    and you are mixing an implicit join (FROM movies, tags) with an explicit JOIN

    and what happened to the HAVING clause i gave you?

    perhaps you had better start over and explain what your latest query needs to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2008
    Posts
    7
    Sorry for being confusing... I'm quite a noob when it comes to SQL.

    The code I pasted was just a snippet of the full code.

    And it didn't work for me when using the HAVING clause, didn't get any results. Executing just the sub query of the HAVING clause gives me a count of 26. I'm not sure if that helps (I got 11 movies in the movies table, and about 120 tags).

    So what I want is to use the code you gave me, and add the possibility so that I can search for title's in the movie table as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •