SQL Query help

[rob willaar]

Could someone help me with a query?
I have a table with the fields actor1,actor2,actor3 and i like to have a
list of all actors starting 'nicola%' from those fields with no duplicate
names. How can i do it with an sql query?

 

[Sasidhar]

If I understood your question properly then this is the simplest way to do
this is

SELECT DISTINCT ACTOR
FROM
(
SELECT ACTOR1 AS ACTOR
FROM TABLENAME
WHERE ACTOR1 LIKE 'nicola%'
UNION
SELECT ACTOR2 AS ACTOR
FROM TABLENAME
WHERE ACTOR2 LIKE 'nicola%'
UNION
SELECT ACTOR3 AS ACTOR
FROM TABLENAME
WHERE ACTOR3 LIKE 'nicola%'
) AS ACTORS

 

[Ben]

UNION returns distinct results by default.

If I understood your question properly then this is the simplest way to do
this is

SELECT DISTINCT ACTOR
FROM
(
SELECT ACTOR1 AS ACTOR
FROM TABLENAME
WHERE ACTOR1 LIKE 'nicola%'
UNION
SELECT ACTOR2 AS ACTOR
FROM TABLENAME
WHERE ACTOR2 LIKE 'nicola%'
UNION
SELECT ACTOR3 AS ACTOR
FROM TABLENAME
WHERE ACTOR3 LIKE 'nicola%'
) AS ACTORS

 

[rob willaar]

Perfect!
Tnx a lot!

Learning all the time...

If I understood your question properly then this is the simplest way to do
this is

SELECT DISTINCT ACTOR
FROM
(
SELECT ACTOR1 AS ACTOR
FROM TABLENAME
WHERE ACTOR1 LIKE 'nicola%'
UNION
SELECT ACTOR2 AS ACTOR
FROM TABLENAME
WHERE ACTOR2 LIKE 'nicola%'
UNION
SELECT ACTOR3 AS ACTOR
FROM TABLENAME
WHERE ACTOR3 LIKE 'nicola%'
) AS ACTORS