mySQL multi-row-compare select

I am not sure if this is the right place to ask this question. It's a mySQL question that I am trying to figure out the best "algorithm" to ge the result that I need.

Consider the following Table "Boxes_Toys". It is a list of boxes and the type of "toys" it contains. Each box can contain toys ranging from 1 to 10.

[B]Table: Boxes_Toys[/B]
[B]Columns: Box => Toys[/B]
b1 => t1
b1 => t2
b1 => t3
b1 => t4

b2 => t1
b2 => t3
b2 => t5
b2 => t7

b3 => t1
b3 => t4
b3 => t6
b3 => t7

I want to write a Query that asks the following:

Select the boxes that ATLEAST HAS Toys t1, t2 and t3

Anyone have any ideas as to what would be the best way to perform a query (or multiple queries?)

A simple method is to take each box's first 3 toys and perform a search for other boxes that have the same toys and then figure out another combination, and so on... When you have 1000 boxes is this still the best way to go about doing it?

Thanks for your time!

#411832

SELECT boxes WHERE box IN (SELECT box WHERE toys = t1) AND box IN (SELECT box WHERE toys=t2) AND box IN (SELECT box WHERE toys=t3);

Something like this?

#411837

thanks Andreas -- I actually tried this before and when you recommended this again I went back to see why would this not work ... then after some searching I noticed that my hosting service uses an older version of mySQL. I need at least version 4.1...

They said they are planning to upgrade to 5 sometime soon... I hope soon. Thanks for your support.

This is all related for a project I am working on called broova™... (about) (preview)

#412511