Simple MySQL query question

Question

bins
----
id      min      max
1       1        20
2       21       40
3       41       60

pictures
--------
id
3
11
59

Basically, I want to select the highest picture Id and then select from the bin table the bin id it matches. For example, for a pictures.id = 59 (highest), I want bins.id = 3. Can anyone help me with such a query? Something like

SELECT bins.id AS id
FROM bins
    JOIN pictures.id 
    ON bins.min < MAX(pictures.id)
        AND bins.max > MAX(pictures.id)

doesn't appear to work. Thanks!

Answer 1

SELECT id 
FROM bins
WHERE min < (Select Max(id) from Pictures) 
  AND max > (Select Max(id) from Pictures) 

Hope it helps

Max

Answer 2

Try this

   Select id From Bins
   Where (Select Max(id) From pictures)
       Between Min and Max

Answer 3

If the bin limits (bin.min and bin.max) increase with id as in the sample data, then the bin id for the 'max' picture id can be obtained using:

SELECT MAX( bins.id )
FROM   bins
JOIN   pictures
ON     bins.min <= pictures.id
AND    bins.max >= pictures.id

Note the use of <= and => - otherwise the bin limit values will be effectively excluded (e.g. picture.id=41 would not match a bin).

Can also be written:

SELECT MAX( bins.id )
FROM   bins
JOIN   pictures
ON     pictures.id BETWEEN bins.min AND bins.max

This will break if your bin limits are not sorted with bin id.