How to return a list of results in MySQL, WHERE only return one
I'm trying to return a list of films the actor was involved with. The second statements gives several results, but only the first hit get showed when i run the last statement. It's like the last variable only searches for "first-match".
Obviously I'm doing something wrong, and I'm unsure what search word I could use to find the solution (as I believe this question is answered several times already).
SELECT @id := actor_id FROM sakila.actor WHERE first_name = 'Ed' and last_name = 'Chase'; SELECT @film :=film_id FROM sakila.film_actor WHERE actor_id = @id; SELECT title FROM sakila.film WHERE film_id = @film;
you need to do something like:
select title from sakila.film f join sakila.film_actor fa on fa.film_id = f.film_id where fa.actor_id = @id
this joins the last 2 queries into one, and is better than your way or using sub queries in my opinion
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]