complicated (join ?) query, could use some advice

[odoisc]

Hello, I have some trouble finding the right way to select something.

Let's say I have a table with columns "productcode, boxlocation, productposition" . This is for a warehouse where a lot of boxes are stored (each with its own unique location number) and every box has several hundred products in it, all placed behind eachother. The productcode is a number to verify what type/version of product it is, its not a unique id. This column has an index.

When all the data was inserted, there was a check to make sure every combination of productcode/boxlocation was unique. So even if a box has 10 pieces of a specific product, only one row was inserted for it. The position column indicates in what order each product is stored in its box. So the first in the box has position 1 , the second (assuming its different type of product then nr 1) got position 2 in its row, etc



The simple searches that I need to perform are like "select boxlocation from warehouse where productcode='X' " , so that we can find the right box and take that product X out of it. Those kind of things work fine.

But sometimes there are more complex requests about a group of products. What kind of query would I need to find "all boxes that contain a product X and a product Y right behind it" ? I stored the productposition just for this purpose, but I dont know how to use it correctly :s

Is this possible to do, without changing the table structure too much ?

[swampBoogie]

select 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

[odoisc]

swampBoogie, thanks a lot

Haven't tested it yet, but I understand how it should work

[odoisc]

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				
LIMIT $p,20

[r937]

... added a "distinct"/"group by" to make the boxlocation results really unique

LOL

is that like "a little bit pregnant"?

groups are, by definition, unique

if you use GROUP BY, adding DISTINCT doesn't make the groups "really unique"

... a "order by" on a new column for the price

some databases will not let you ORDER BY a column that isn't selected

especially when there's grouping involved

after the grouping, you have a bunch of box locations, and you can't sort on the price, because by then the groupings don't have a price column any more