Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Unanswered: query or algorithm?

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2008
    Posts
    4
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Got it. Thank you. I missed that requirement in the posters example. It all makes sense now.

  8. #8
    Join Date
    Sep 2008
    Posts
    4
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    napy, you shoulda quit while you was ahead

    the results in your last post do not make any sense at all

    have you tried either of my queries?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2008
    Posts
    4
    The hole box size is 3000 and less than the box 500 with size 4000.
    Your way is nice to find one box that includes all boxtypes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •