At 11:13 AM 19/05/2006, you wrote:

>This is probably a real easy question but I've never had to do it
>before and for the life of me I can't figure it out.
>
>I have a table that lists actor id's and title id's for a video
>database and I just need to list actor id's where there are duplicate
>title id's.
>e.g.
>
>actor_id title_id
>1 1
>2 1
>3 2
>
>and the result would be
>
>actor_id
>1
>2

Assuming this has a unique constraint across (actor_id, title_id) then

select actor_id from actor_title
where title_id = ?

The questionmark here could be a replaceable parameter or it could be
a constant value for title_id.

Now, supposing this table is an intersection between two tables
(Actor and Title), you are more likely in real life to be wanting,
for example, a list of the names of the actors appearing in a given
title. Such an intersection table resolves a many:many relationship
(a title can involve many actors, while an actor can appear in many
titles). You can extract the info you want, e.g. a list of the
actors' names for a given title, by using joins.

We don't know your table structures, but one could guess that the PK
of Actor is actor_id and the PK of Title is title_id. :-) And that
the actor names are fields in Actor, while the Title is a field in Title.

Suppose you want to list the actors appearing in a video named 'The
Clockwork Orange'. Here's how you would get it:

select
a.First_name || ' ' || a.Last_name as Actor_name
from Actor a
join Actor_Title at
on a.actor_id = at.actor_id
join Title t
on t.title_id = at.title_id
where t.title = 'The Clockwork Orange'

Suppose that you have an application that lets the user select a
title from a list that has behind it a dataset extracted from the
Title table, that includes the title_id. The app will then know the
title_id of the video that the user selects and can pass that to the
query instead.

Now, we don't have to hard-code the title of the video in the query
at all. Your application interface will have some mechanism for
passing a parameterised statement. Some drivers use the '?' symbol
directly and rely on you passing values for parameters in strictly
correct order; others encapsulate the mechanism and use their own
convention to allow you to have named parameters. I'll use
ObjectPascal as an example, which allows named parameters and uses
the convention of prefixing them with a colon:

select
a.First_name || ' ' || a.Last_name as Actor_name
from Actor a
join Actor_Title at
on a.actor_id = at.actor_id
join Title t
on t.title_id = at.title_id
where t.title_id = :title_id

This statement is prepared once and can be executed as many times as
you like, simply by having your app pick up the title_id selected by
the user, passing its value to the parameter and executing the
prepared statement afresh.

If, on the other hand, you really have one table that's serving the
purposes of all three, then you have some normalisation to do yet...

./heLen