SQL Query help

  • Thread starter Thread starter rob willaar
  • Start date Start date
R

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?
 
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
 
UNION returns distinct results by default.

Sasidhar said:
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
Click to expand...
 
Perfect!
Tnx a lot!

Learning all the time...

Sasidhar said:
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
Click to expand...
 
Back
Top