query or algorithm?

napy · #1 (**permalink**) 09-11-08, 00:41

hello,
i have these two tables:
CREATE TABLE table1 (
boxid INT NOT NULL,
balltype INT NOT NULL,

UNIQUE (boxid, balltype)
);
CREATE TABLE table2 (
boxid INT NOT NULL,
size INT NOT NULL,

UNIQUE (boxid)
);
As you can see each box has a size and there can be lots of different balls in the boxes.
Now i need a query that returns at least one of each balltype and it should have the minimum size for the boxes.
Is there a way to solve this via mysql?

r937 · #2 (**permalink**) 09-11-08, 06:02

Code:

SELECT table1.balltype
     , MIN(table2.size) AS min_size
  FROM table1
LEFT OUTER
  JOIN table2
    ON table2.boxid = table1.boxid
GROUP
    BY table1.balltype

napy · #3 (**permalink**) 09-11-08, 14:12

Sorry, i didn't make this clear enough.
The minimum size for the boxes that have at least one of each boxtypes and not the minimum size for the balltypes.
Here is an example:

boxid balltype
500 1
500 2
500 3
501 1
502 2
503 3
504 1
505 2

boxid size
500 2000
501 1000
502 1000
503 1000
504 3000
505 4000

the query should return:

boxid balltype size
500 1 2000
500 2 2000
500 3 2000

r937 · #4 (**permalink**) 09-11-08, 14:31

Code:

SELECT table1.balltype
     , MIN(table2.size) AS min_size
  FROM table1
LEFT OUTER
  JOIN table2
    ON table2.boxid = table1.boxid
 WHERE table1.balltype IN
       ( SELECT balltype
           FROM table1
         GROUP
             BY balltype
         HAVING COUNT(*) =
                ( SELECT COUNT(DISTINCT balltype)
                    FROM table1 ) )       
GROUP
    BY table1.balltype

buckeye234 · #5 (**permalink**) 09-11-08, 16:34

Isn't that a bit more complicated than necessary? Due to the unique constraints defined in the ddl, we know that every row in table1 is unique and a box has only one row, and therefore one size, in table2. Therefore, we don't really need the MIN function and can do a simple join left outer join as in your original answer. In the code below, I just added the boxid column to your original response and dropped the MIN function.

I don't do a lot of SQL programming, so I am just wondering for my own learning. Wouldn't this work and be more straightforward?

Code:

SELECT table1.boxid
     , table1.balltype
     , table2.size AS min_size
  FROM table1
LEFT OUTER
  JOIN table2
    ON table2.boxid = table1.boxid
GROUP
    BY table1.balltype

r937 · #6 (**permalink**) 09-11-08, 16:44

Quote:

Originally Posted by buckeye234

Therefore, we don't really need the MIN function

well spotted, that's correct

i must have been misled by the requirement "it should have the minimum size for the boxes"

your query is missing some way to ensure that only boxes that have all balltypes are returned

buckeye234 · #7 (**permalink**) 09-11-08, 19:59

Got it. Thank you. I missed that requirement in the posters example. It all makes sense now.

napy · #8 (**permalink**) 09-11-08, 21:43

here is another example:

boxid balltype
500 1
500 2
500 3
501 1
502 2
503 3
504 1
505 2

boxid size
500 4000
501 1000
502 1000
503 1000
504 3000
505 4000

result:

boxid balltype size
501 1 1000
502 2 1000
503 3 1000