Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Unanswered: Need help, don't know where to start?

    Hi, i have a database with a table that records all the types of stones, eg.

    ID Size Shape

    1 1.1 round
    2 1.2 square
    3 1.3 round
    4 2 round
    5 2.1 square
    6 2.5 round
    7 3.7 square
    8 3.9 square
    9 4.1 round
    10 4.9 square
    11 5 round

    What i MUST do is sort it into individual shape, also for every size's range i must display a sub-heading like..

    ROUND SHAPE - 1.0 to 1.9
    {display all stones within 1.0 to 1.9 which is round}
    ROUND SHAPE - 2.0 to 2.9
    {display all stones within 2.0 to 2.9 which is round}
    ROUND SHAPE - 3.0 to 3.9
    {display all stones within 3.0 to 3.9 which is round}
    ROUND SHAPE - 4.0 to 4.9
    {display all stones within 3.0 to 3.9 which is round}
    ROUND SHAPE - 5.0 to 5.9
    {display all stones within 5.0 to 5.9 which is round}

    SQUARE SHAPE - 1.0 to 1.9
    {display all stones within 1.0 to 1.9 which is Square}
    SQUARE SHAPE - 2.0 to 2.9
    {display all stones within 2.0 to 2.9 which is Square}
    SQUARE SHAPE - 3.0 to 3.9
    {display all stones within 3.0 to 3.9 which is Square}
    SQUARE SHAPE - 4.0 to 4.9
    {display all stones within 4.0 to 4.9 which is Square}
    SQUARE SHAPE - 5.0 to 5.9
    {display all stones within 5.0 to 5.9 which is Square}

    to create multiple sql statement then displaying them in a loop seems to be a messy task, could someone please give me a hint on how to write a good sql statement to sort this table out in an organized way.

    Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the order by clause of sql

    id expectg to see something like
    select ID, Size, Shape, from <mytable> order by Shape,Size

    the rest of it is down to your presentation layer, incidentally what are you suing for your presentation layer (Access, VB, C++, MFC/OWL, Delphi?)
    you could probably do it via a sub select wihtin MySQL, but that wold be messy

    incidentally Id expect the Shape column to be a reference to another table and be somehting like ShapeTypeID

    perhaps a quick refresher on table desing would be in order

    tblShapeTypes
    ShapeTypeID: ShapeDesc
    0: Unknown
    1: Round
    2: Ovoid
    3: Square
    4: BuckyBall

    tblGems
    GemID,Size,ShapeTypeID
    1 1.1 1
    2 1.2 3
    3 1.3 1
    4 2 1
    5 2.1 3
    .....
    11 5 1
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2006
    Posts
    3

    Thanks healdem

    thank you for your reply,

    so far what i find is the most difficult is not the shape part, i got that handle in a different table, using foreign key and all, however selecting the range from 1.0 to 1.9 then select another 2.0 to 2.9.....this is just very messy. any suggestions on this?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one technique could be to create a grouping table

    tblSizeReportingGroup
    SizeGrpID SizeGrpSeq SizeGrpDesc MinSize MaxSize
    1 1 "size range 1 to 1.9" 1.0 1.9
    2 3 "size range 3.0 to 3.9" 3.0 3.9
    2 2 "size range 2.0 to 2.9" 2.0 2.9
    ......
    10 5 "size range 5.0" to 5.9 5.0 5.9
    the sequence (SizeGrpSeq) may not be neccessary if you know that there are never going to be any more size ranges, and you put the ranges in numerical order. Id put it in so that I could add a new range to the table and adjust the sequence accordingly
    the description is optional

    add a column to the tblGems which references the SizeGrpID
    or do a function or SQL query which finds the size grp & sequence
    adding a column to the tblGems isnt neccesarily smart - it means you have a maintenance task if the requirement changes (say the users want to spolit the groups 1.0 to 1.4, 1.5 to 1.9) - you have to update the tblGems to reflect the new groupings personally Id use a fucntion or part of a where clause to identify the group

    you don't actually need the table
    you could issue a series of SQL statements that extract the groupings
    you could encode the groupoing using a lookup with the SQL (nasty solution DONT go down that route)
    you could handle this within the presentation layer
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually the separate lookup table is a good solution, provided you don't do something boneheaded like store the FK in your main table

    do a simple join using a range condition
    Code:
    select ...
         , tblSizeReportingGroup.SizeGrpDesc 
         , ...
      from tblGems
    inner
      join tblSizeReportingGroup
        on tblGems.Size 
           between tblSizeReportingGroup.MinSize 
               and tblSizeReportingGroup.MaxSize
    then you can completely re-assign size ranges at will, and the query will still work

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

  6. #6
    Join Date
    Oct 2006
    Posts
    3
    thanks guys, i will give it a try, will keep you guys inform on the result.
    thank you very much.

Posting Permissions

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