It works correctly, but it's pretty slow sometimes :s
Any advice on how to use indeces for this ? Should I use a combined index for the "on" and "where" parts, or give each their own index ? I dont mind the extra hd space, as long as it searches fast enough to be useable on a site. Several of the tests gave me times of 50 to 2 seconds, whereas I consider 0.5 seconds acceptable for a query.
I extended the query a bit too, added a "distinct"/"group by" to make the boxlocation results really uniqe, a "order by" on a new column for the price and a LIMIT. And probably made it even more complicated then it already was
select DISTINCT (b1.boxlocation)
from box as b1 inner join box as b2
on b1.boxlocation = b2.boxlocation
where b1.productcode = 'X'
and b2.productcode = 'Y'
and b1.productposition + 1 = b2.productposition
GROUP BY b1.boxlocation
ORDER BY b1.price DESC