quote:
Originally posted by toddy
quote:
Originally posted by matty
toddy yours wouldn't work because you are checking if tblFilms.Actor1 = Name1 AND tblFilms.Actor2 = Name1 AND etc.....
you can tell i haven't used a query in a long time 
It's all good. I test software and a lot of stuff I write database queries for.
There is a better way of doing it I just cannot think of it off the top of my head
toddy, you were correct about INNER JOINing the tables here is how you could do it.
SQL code:
CREATE TABLE #TEST ( ID INT, Actor1 VARCHAR(50), Actor2 VARCHAR(50), Actor3 VARCHAR(50), Actor4 VARCHAR(50) )
INSERT INTO #TEST
SELECT 0, 'w', 'x', 'y', 'z'
DECLARE @name1 AS VARCHAR(50);
DECLARE @name2 AS VARCHAR(50);
SET @name1 = 'x';
SET @name2 = 'z'
SELECT A.* FROM #TEST A
INNER JOIN #TEST B ON B.ID = A.ID
AND (B.Actor1=@name2 OR B.Actor2=@name2 OR B.Actor3=@name2 OR B.Actor4=@name2)
WHERE (A.Actor1=@name1 OR A.Actor2=@name1 OR A.Actor3=@name1 OR A.Actor4=@name1)
Returns:
ID Actor1 Actor2 Actor3 Actor4
0 w x y z
You just need to join the table on itself on the primary key. However this is more strenuous on the database as you are actually quering all the rows twice.
/ 
