Tuesday, March 27, 2012

Comparing tables

I have two tables in my db, Actor and Movie. Both of them contain a field with ActorID, and Id like to put together a query that returns the ones that exist only in Actor.ActorID, and not the ones that exist in both Actor.ActorID and Movie.ActorID.

Is this something anybody could help me with?

Thanks..

/AndreasSelect a.*
from actors a
LEFT OUTER JOIN
movies m ON
a.actor_id = m.actor_id
where m.actor_id is NULL;|||Originally posted by r123456
Select a.*
from actors a
LEFT OUTER JOIN
movies m ON
a.actor_id = m.actor_id
where m.actor_id is NULL;

One way of doing this is

select a.actor_id
from actors a
where not exists (
select 1
from movies m
where m.actor_id=a.actor_id)

you can also use

select a.actor_id
from actors a
where a.actor_id not in (
select actor_id
from movies
)

No comments:

Post a Comment