Shoutbox

Okay, I have a database of films.....I want a search function to be able to find any films that a given pair of actors appear in (Owen Wilson & Ben Stiller, for example) when they could be in any one of the 4 "Actor" fields.

Here's the code I have:

SQL code:

---

SELECT tblFilms.ID, tblFilms.Title, tblFilms.Year, tblFilms.Actor1, tblFilms.Actor2, tblFilms.Actor3, tblFilms.Actor4
FROM tblFilms
WHERE (((tblFilms.Actor1)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor1)=[Enter the second actor who's films you would LIKE TO find])) OR (((tblFilms.Actor2)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor2)=[Enter the second actor who's films you would LIKE TO find])) OR (((tblFilms.Actor3)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor3)=[Enter the second actor who's films you would LIKE TO find])) OR (((tblFilms.Actor4)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor4)=[Enter the second actor who's films you would LIKE TO find]));

---

That's the closest I could get.....but as you'll know, that finds any film which EITHER actor was in.  My SQL knowledge isn't good enough to figure out how to get it to work properly, but I'm sure it'll be easy enough for someone who knows what they're doing!

Thanks

The best I can come up with is:

SQL code:

---

SELECT those_columns FROM tblFilms WHERE (tblFilms.Actor1=NAME1 OR tblFilms.Actor2=NAME1 OR tblFilms.Actor3=NAME1 OR tblFilms.Actor4=NAME1)
INTERSECT
SELECT those_columns FROM tblFilms WHERE (tblFilms.Actor1=NAME2 OR tblFilms.Actor2=NAME2 OR tblFilms.Actor3=NAME2 OR tblFilms.Actor4=NAME2)

---

But I dunno if your SQL engine supports INTERSECT (at least MySQL doesn't seem so). But if you're just doing an assignment then I think it's fine.

I'm using Microsoft Access 2007....when I try using INTERSECT it says there's a syntax error and missing operation

Well, it appears that it's not supported on Access

Anyway, when I googled a lot people say INTERSECT can be implemented using INNER JOIN, but I'm not sure how it works exactly. You'll have to fiddle with it.

It's a Report that takes its info from the query

edit:
maybe not

Hmm, yeah, it seems to have a problem with "INNER"

From the looks of it you have to do Inner Join ON something

http://msdn.microsoft.com/en-us/library/bb208854.aspx

You don't want to use an INNER JOIN in this case as an INNER JOIN will join 2 tables with like values.

SQL code:

---

SELECT * FROM tblFilms
WHERE (Actor1=NAME1 OR Actor2=NAME1 OR Actor3=NAME1 OR Actor4=NAME1)
    AND (Actor1=NAME2 OR Actor2=NAME2 OR Actor3=NAME2 OR Actor4=NAME2)

---

toddy yours wouldn't work because you are checking if tblFilms.Actor1 = Name1 AND tblFilms.Actor2 = Name1 AND etc.....

We have a winner.....thanks Matty

No problem djdannyp.

Here is an example