mySQL JOIN columns and count distinct on one table and get more values on other table

I'm doin开发者_开发知识库g a db to organize my movies. I have two tables (1 for movies one for actors). Putting it simple and omitting useless fields for this exercise

Table MOVIE: 
    id_movie | name_movie | star1 | star2 | star3 | runtime

Table ACTOR: 
    id_actor | name | sex | image

There are movies where the star1 is for example "Morgan Freeman" but on other movies "Morgan Freeman" is in the field star2.

I wanna count DISTINCT values from the UNION of star1, star2, star3 from the table MOVIE and then go get the actor/actress sex and image on the table ACTOR.

Already tried all different kinds of queries but no results so far :(

Can anyone help me on this one?

---

@Tom Green's solution is very close... except needing the "DISTINCT" in his query.

However, distinct actors, you already have an actor table, query from that directly, that SHOULD be unique of actors and actresses regardless of the movie they were featured in.

However, if you wanted to know distinct actor/actresses who were in the same MOVIE as someone else, I would actually adjust your database structure to a more flexible structure allowing as many "stars" as you need by using a "bridge" or "link" table... offering 1:many relationship

Table MOVIE: 
    id_movie | name_movie | star1 | star2 | star3 | runtime


Table MovieActor:
    id_movieactor  | id_movie | id_actor (star)

Table ACTOR: 
    id_actor | name | sex | image

to populate the MovieActor table here, you could...

insert into MovieActor ( id_movie, id_actor )
   select id_movie, star1 as id_actor
      from movie
      where star1 > 0
   union
   select id_movie, star2 as id_actor
      from movie
      where star2 > 0
   union
   select id_movie, star3 as id_actor
      from movie
      where star3 > 0

This way, you are unlimited in number of stars per movie, yet can have a minimum of 1 without wasted space of the others... Then, in the future, you want to adjust your queries by adding a possible 4th star to movies, how many queries will you be adjusting...

Anyhow, once this is done, you can query as simple as

select distinct ma.id_actor, actor.*
   from MovieActor ma join Actor on ma.id_actor = actor.id_actor

If you had some other examples of types of queries you might be posing to get out of your database, I could easily offer supplemental queries...

---

It looks like you're joining with ACTOR using the NAME field which is a bit weird--ideally you'd want to join using ACTOR_ID, but anyway, the query below should do what you want under these assumptions:

select name, sex, image 
from actor
where name in 
(select star1 as star from movie
 union
 select star2 as star from movie
 union
 select star3 as star from movie)