Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: Comparing tables

    I have two tables in my db, Actor and Movie. Both of them contain a field with ActorID, and Iīd 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..

    /Andreas

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select a.*
    from actors a
    LEFT OUTER JOIN
    movies m ON
    a.actor_id = m.actor_id
    where m.actor_id is NULL;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    51
    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
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •