Re: SQL problem is killing this newbie

Thanks Matthew, I can see the advantages of the table structure you recommend, but what would be the select statement to find people who have seen both "Fear and loathing" AND "Monkey Town"? Matthew Crouch <whyyousee@yahoo.com> wrote in message news:3C17C220.29D8C601@yahoo.com... > You need an associating table; members and movies are in a many-to-many > relationship here. > The associating table represents the act of viewing-a-movie, and describe who > and what are involved > > comme ca: > > "members" > memberid, name > 1 Fred > 2 Jack > 3 Jill > > "movies" > movieid, title > 1 Fear and loathing > 2 Monkey Town > 3 PHP: The film > 4 PHP: The sequel > > "viewings" > viewingid, memberid, movieid > 1 2 2 > 2 1 4 > 3 1 1 > 4 3 4 > this data indicates that Jack saw "monkey town", Fred saw > "Fear&Loathing" and > "PHP:The Sequel", and Jill saw only "PHP: The sequel" > > Samios wrote: > > > I am having a problem designing a query on a mysql 3.23 database. > > > > I have 2 tables - "member" and "movie". > > The "member" table stores the people details, the "movie" table > > records the > > movies they have seen (each record is one movie). > > > > MEMBER > > memberid int not null auto_increment primary key, > > membername varchar(50) not null, > > etc... > > > > MOVIE > > movieid int not null auto_increment primary key, > > movietitle varchar(50) not null, > > memberid int, > > index (memberid) > > etc... > > > > Obviously member to movie is a one-to-many relationship. > > > > I want to create a search page which will allow me to search for members who > > have watched a specified set of movies. > > i.e. I want to query the database for members who have seen "Star Trek" AND > > "Star Wars" AND "Planet of the APES". > > > > I can create a query which searches for "Star Trek" OR "Star Wars" OR > > "Planet of the Apes". > > Unfortunately the "AND" condition is causing me problems. > > > > I'm also hoping to use the "LIKE" operator in this query. > > e.g. where movietitle like "%star%". > > This will give me added flexiblity down the track. > > > > Any help would be greatly appreciated. >