SQL Query Help - Select from 2 tables RRS feed

  • Question

  • I have a database with two tables I'm using for a movie collection.  The DB has tables "movies" and "actors".  Table actors has columns ID and actor.  Table movies has columns ID,title,actors.  I want to return the title based on a search for an actor.  The column actors (in the movies table) holds a list of ID values (type of string), from the table "actors".  Given a search value of an actors name, how do you search the table actors for the name (to get the ID value for the name) and then look thru the list in the column actors (in the moves table) and return records from the movies table.

    The primary key "ID" in both tables is an integer.  The actors column in the movies table is type of text, and contains a list, separated by a comma, of numbers corresponding to the ID in actors - such as "1,14,23".  Then the table actors would contain info such as: 1-Harrison Ford, 14-Matt Damon, etc.

    I'm using this in an HTA using Javascript and ActiveXObject("ADODB.Connection") to make connections to the MS Access database. Data provider = Microsoft.ACE.OLEDB.12.0 For Access 2007.

    I'm thinking I need to query the actors table to find all the ID values that match the search string.  Then build another query with a bunch of "OR" statements looking in the movies table, finding where column actors LIKE the ID found in the first query.  Then I would have to go thru these results and make the actors column an array of values (using string.split(",") ) and testing each of these values against the ID values returned from the first result.  This last step is needed for the following scenario: You have actors with ID values such as 1,11,20.  If you have a movie with actors 1,20.  And another movie with actors 11,20.  And your search returns actor "1" in the first query I mentioned above...the second query will return both movies since it finds "1" and "11" using the "LIKE" in the second query.

    But I thought maybe SQL might make this easier in one quick query?

    Sunday, April 26, 2009 6:02 AM