How to return a list of results in MySQL, WHERE only return one


Problem :

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;

Second statement results Third statement returns



Solution :

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


Mysql Tutorials

Mysql Howto..

How to Replace a Word in a MySQL Table [closed]

How to store the apostrophe into MySQL database from user input? [duplicate]

How can I make a field default a value with every new record?

mySQL how to prevent insert unless combination of two columns (fields) are unique? [duplicate]

How to Skip Records of MySQL table using PHP?

How to delete duplicate records in mysql database?

How to determine what part of an AJAX application is slowing things down? [closed]

How to obtain the value of the result in a `node-mysql` query

Show all table rows in
Try with this you will get you have made a mistake in while loop <?php $con = mysql_connect("localhost","x","x"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("x", $con);...
Read more

NodeJS: How to stop code execution till create operation to MySQL completes

How to graph mysql data in python?

MYSQL How to find all records which date is biggest

How to sync Map <-> MySQL in Java?

Spring, iBatis, MySQL - how to manage transactions?

How to select from mysql data with min and max timestamp for each group of rows

How do i get the results of a mysql query into html textbox

MySQL - How can I use left join to get exactly one row from a joined table in the on clause?

DataTable.Load shows less rows than source DataReader

How do I fix the 'Out of range value adjusted for column' error?

how to count the num of records in a group in mysql

How can I configure database.yml from an existing database on bluehost?

How to get the first process ID in MYSQL of the current user?

How to retrive data from server using NIO

MySQL : How to count multiple row where datediff() is less than 30

How to find problems in MySQL stored procedure?

How to write a (MySQL) “LIMIT” in SQL Server?

how to update all the entries except one in a particular column using Mysql

MySQL SELECT with JOIN - how to code correct

What are examples of how to code a MySql Connection string? [closed]

How to decrypt the key