Simple MySQL query 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. Fo开发者_高级运维r 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!

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

Hope it helps

Max

Try this

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

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.