possible to do w/ a single sql statement

Anon

I am trying to think of a neat way to execute a query that will work in mysql but the only ideas I have involve subqueries which won't work. Say you have a database from a film critic for example in one table you have films

Films:
id title

1 Old Yeller
2 Evil Dead
3 One Crazy Summer
4 Breakfast at Tiffany's

You have another table of preset descriptors for the films

Descriptors:
id descriptor

1 scary
2 silly
3 funny
4 sad
5 romantic
6 happy
7 classic

Finally you have a table that links films with descriptors and a film could have more than one:
id film_id descriptor_id

1 1 4
2 1 7
3 2 1
4 2 2
5 2 3
6 2 7
7 3 3
8 3 2
9 3 5
10 3 6
11 4 5
12 4 6
13 4 7

Site users can select from a checkbox form what kind of film they want and they can look using multiple criteria. If the user checks romantic they get Breakfast at Tiffany's and One Crazy Summer, if they check romantic and classic that should only return Breakfast at Tiffany's. If they check silly and funny they should get Evil Dead and One Crazy Summer, if they added scary to the list they'd just get Evil Dead. You probably get the idea. Any way is there any way to grab an appropriate result set like that in mysql?

Anon

select title from films f
join titledescriptors td on
f.id = td.film_id
join descriptors d on
td.descriptor_id = d.id
where d.descriptors in ('scary','silly')
group by title
having count(*) = 2

So, you need to construct the statement dynamically

Why do you have the id column in the table titledescriptors?

bastien

why not store the multiple values of the descriptor in one line like:

id movie descriptor
1 1 1,2
2 2 2
3 3 4,5,6

take all the from the form, store them in an array and post the array to the DB?

Easier to read and way less data...

Bastien

Anon

One of the worst possible design choices.

Stay with the (apart from the id column in the titledescriptors table) data model you have charmkpc.