how to query without defining table initial name?

Question

Hi,

I have following tables.

genre:
  genre_id
  name

actors:
  actor_id
  name

movies:
  movie_id
  actor_id
  genre_id
  title

I have following query to select all the actors with genre_id 3.

select a.name, m.genre_id from 
actors as a
, movies as m
where
m.genre_id = 3;

is it possible to make a query without "movies as m" since I do not need m.genre_id. I just want to display actors name.

Answer 1

Have to join to movies if the genre_id is the only info you have....

select a.name
from actors as a
inner join movies as m on a.actor_id = m.actor_id
where m.genre_id = 3;

Answer 2

CSharpAtl has it, but as an alternative:

  select a.name
    from actors a
    where actor_id in (select m.actor_id 
                         from movies m 
                        where a.actor_id = m.actor_id
                          and genre_id = 3)