Question 20460 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql first look value and then search with value find

So I have two tables :

Film :

id | title | year | Director

Casting :

id | title | year | actors

So What I am trying to do is to find all the actors that have the same title as the director.

So what I am trying to do first is

This will return me the film name.
And with the film name I want to do a second research to find all the actors that are in that film

So first :

Select title WHERE Director="name"

THEN Select Actor WHERE title="The first value I am looking for"

One example I am looking for Starwars movie.

My table information :

FILM

id:0 | title: "Starwars" | productor : "George Lucas"

Casting:

id:0 | title: "Starwars" | Acteur : "Ewan McGregor"

id:1 | title: "Starwars" | Acteur : "Natalie Portman"

So my MySQL should return only the name actors

Acteur : "Ewan McGregor", "Natalie Portman"

Thanks a lot of your help!

Tags:

2

Answers


  1. 0 
    SELECT title,
       GROUP_CONCAT(casting.actor) actors
FROM casting
JOIN film USING (title)
WHERE film.director = 'George Lucas'
GROUP BY title;
    Login or Signup to reply.
  2. 0

    You can do it using simple query as :

    SELECT GROUP_CONCAT(Casting.actors SEPARATOR ', ')
FROM Film
JOIN Casting ON Film.title = Casting.title
WHERE Film.Director = 'director_name'
AND Film.title = 'film_title';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search