1

I was playing around with Sakila and I ran across the actor_info view and one of its columns is film_info which is a detail of all the movies one actor has worked in. For example a query would be:

select * from actor_info;
# actor_id | first_name | last_name | film_info
    1      | PENELOPE   |   GUINESS |  Animation: ANACONDA CONFESSIONS; Children: LANGUAGE COWBOY; Classics: COLOR PHILADELPHIA, WESTWARD SEABISCUIT; Comedy: VERTIGO NORTHWEST; Documentary: ACADEMY DINOSAUR; Family: KING EVOLUTION, SPLASH GUMP; Foreign: MULHOLLAND BEAST; Games: BULWORTH COMMANDMENTS, HUMAN GRAFFITI; Horror: ELEPHANT TROJAN, LADY STAGE, RULES HUMAN; Music: WIZARD COLDBLOODED; New: ANGELS LIFE, OKLAHOMA JUMANJI; Sci-Fi: CHEAPER CLYDE; Sports: GLEAMING JAWBREAKER

So I'd like to make a selection of actors who have worked in documentaries or animation but i don't know if this is possible. Sorry if the question is too basic. Thanks!

  • Do you mean you want to get field_name and last_name of rows where film_info starts with, or contains, "Animation or Documentary"? – Jehad Keriaki May 13 '15 at 15:24
  • When INSERTing, pick out selected fields and make them into columns. Then put the rest (or all) into film_info. – Rick James May 13 '15 at 20:40
1

WITHOUT INDEXING

SELECT * FROM actor_info
WHERE film_info LIKE '%Animation%' OR film_info LIKE '%Documentary%';

or

SELECT * FROM actor_info
WHERE LOCATE('Animation',film_info)>0
OR LOCATE('Documentary',film_info)>0;

WITH INDEXING

Create a FULLTEXT index on film_info

ALTER TABLE actor_info ADD FULLTEXT (film_info);

Query using the FULLTEXT index

SELECT * FROM actor_info
WHERE MATCH (film_info) AGAINST ('+Animation*' IN BOOLEAN MODE)
UNION
SELECT * FROM actor_info
WHERE MATCH (film_info) AGAINST ('+Documentary*' IN BOOLEAN MODE);
  • Thanks! What I wonder too is if the content of the film_info column can be filtered so it can show that Penelope worked in Anaconda Confessions without showing the rest. – ffuentes May 22 '15 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.