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!

Massimo Fazzolari · Accepted Answer · 2010-02-06 01:24:15Z

2

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

Hope it helps

Max

edited Feb 6, 2010 at 1:24

answered Feb 6, 2010 at 1:06

Massimo Fazzolari

5,2323 gold badges30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ErikE Over a year ago

Does MySQL require the ANY keyword? Because the subqueries will only return one value, so ANY, ALL, SOME, or nothing should mean the same thing here, no?

Charles Bretana · Accepted Answer · 2010-02-06 01:09:02Z

1

Try this

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

answered Feb 6, 2010 at 1:09

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Comments

martin clayton · Accepted Answer · 2010-02-06 01:19:58Z

0

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.

answered Feb 6, 2010 at 1:19

martin clayton

78.6k33 gold badges220 silver badges201 bronze badges

Collectives™ on Stack Overflow

Simple MySQL query question

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related