SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Distinct Group By problem

    Hi,
    I've got a table that has:
    a unique ID (proId)
    a product ID (proawningId)
    a "upto" width column (prowidth)
    an "arm length" column (proarms)

    The table is for patio awnings and it stores what projection arm length you'll need for various widths of awnings. For instance if you want an awning 2.75 metres width you look at the data find your width in the width column (although this column shows the maxium width for each awning and arm combination) so an entry might be upto width 2.98 = arm 2.5m for awning type 1.

    What I'm trying to do is to create a quick look-up that populates a select drop down list. A user enters their width and clicks submit the system then queries the database and populates the select list with the possible "arm" lengths they can have.

    This is the SQL statement I'm currently using:

    SELECT proarms, proId, proawningId, prowidth FROM ukawnings.tblawningprojections WHERE proawningId = awningId AND prowidth >= (this is the width entered on the form) GROUP BY proawningId ORDER BY proarms ASC

    This almost works but I get a separate entry for each arm length for each awning type, e.g.

    if I enter 2.75m as my width the select list looks like:

    2.5M (awning type 1)
    2.5M (awning type 2)
    2.5M (awning type 3)
    3.0M (awning type 4)
    3.5M (awning type 5)

    What I'd like to happen is there to be only one entry for each Arm length (so only one 2.5M in the example above). I tried adding DISTINCT(proarms) to the SQL but it doesn't make any difference.

    Can anyone help.
    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why did you decide to use GROUP BY?

    also, could you please do a SHOW CREATE TABLE? you initially said there were 4 columns, and then you started talking about awning type, but type isn't one of the columns

    a few sample rows of data would help too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    Thanks for your repsonse.

    I used GROUP BY because each "awning type" (proawningId) will have a different arm length for each "upto" width, for example:

    awning type----upto width----arm length
    1--------------1.78----------1.50
    1--------------2.30----------2.00
    1--------------2.73----------2.50
    1--------------3.21----------3.00

    so if I entered 2.5 as my desired width I just want to see the entry (for this awning type) with the arm length that matches my "width" - in this case that would be "upto" 2.73 and the arm length would be 2.5.

    When I tried the SQL without the GROUP BY clause it was also showing all the "arm sizes" greater than my entered width (in the example above it would also return Arm length 3.0 as the width 3.21 is also greater than my entered width of 2.5

    The awning type is the column "proawningId"

    Here is the CREATE TABLE SQL for this table with some mock content:

    ************************************************

    -- phpMyAdmin SQL Dump
    -- version 2.11.4
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Oct 10, 2009 at 09:21 AM
    -- Server version: 5.1.38
    -- PHP Version: 5.2.10

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

    --
    -- Database: `ukawnings`
    --

    -- --------------------------------------------------------

    --
    -- Table structure for table `tblawningprojections`
    --

    CREATE TABLE IF NOT EXISTS `tblawningprojections` (
    `proId` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `proawningId` int(11) unsigned DEFAULT '0',
    `prowidth` decimal(8,2) unsigned DEFAULT '0.00',
    `proarms` decimal(8,2) unsigned DEFAULT '0.00',
    PRIMARY KEY (`proId`),
    KEY `proId` (`proId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

    --
    -- Dumping data for table `tblawningprojections`
    --

    INSERT INTO `tblawningprojections` (`proId`, `proawningId`, `prowidth`, `proarms`) VALUES
    (1, 1, 1.78, 1.50),
    (2, 1, 2.30, 2.00),
    (3, 1, 2.73, 2.50),
    (4, 1, 3.21, 3.00),
    (5, 2, 2.03, 1.50),
    (6, 2, 2.55, 2.00),
    (7, 2, 2.98, 2.50),
    (8, 2, 3.46, 3.00),
    (9, 3, 2.65, 1.50),
    (10, 3, 2.65, 2.00),
    (11, 3, 2.98, 2.50),
    (12, 3, 3.46, 3.00),
    (13, 4, 2.03, 1.50),
    (14, 4, 2.55, 2.00),
    (15, 4, 2.98, 2.50),
    (16, 4, 3.46, 3.00),
    (17, 4, 3.97, 3.50),
    (18, 4, 4.22, 3.75);

    ************************************************

    Thanks in advance for your time.
    Last edited by scim1971; Oct 10, 2009 at 01:34. Reason: formatting

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, that looks fine

    try this --
    Code:
    SELECT proarms
         , proId
         , proawningId
         , prowidth 
      FROM ukawnings.tblawningprojections 
     WHERE proawningId = awningId 
       AND prowidth = 
           ( SELECT MIN(prowidth)
               FROM ukawnings.tblawningprojections 
              WHERE proawningId = awningId 
                AND prowidth >= width )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    Thanks again for your swift reply. I tried the SQL you suggested but didn't get the results I expected.

    I tried a "width" of 2.4m and with my original SQL statement I have the following results:

    Arm length:
    1.5
    2.0
    2.0
    2.5

    which is correct but all I want to show is 1.5, 2.0, 2.5 (I only want one entry for each "arm").

    When I tried your SQL the results were:
    2.0
    2.0

    (by the way the "proawningId = awningId" was something I accidentally left in my SQL from an earlier version and can now be removed).

    Sorry to be a pain and again, thanks for your time.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i guess i don't understand your requirements

    you described this scenario --
    so if I entered 2.5 as my desired width I just want to see the entry (for this awning type) with the arm length that matches my "width" - in this case that would be "upto" 2.73 and the arm length would be 2.5.
    which clearly shows that you want only one matching result

    but today you say it should return this --

    Arm length:
    1.5
    2.0
    2.5

    so obviously i have misunderstood your requirements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I probably haven't explained it very well.

    In the "arms" table (tblawningprojections) there are lots of different "awning types" (proawningId) - each awning type might have different "arm lengths" (proarms) for different "awning widths" (prowidth).

    So if a user enters (for example) a width of 2.4 the system looks at the database table and should find which possible arm lengths are available for that width. Using the data in my table (shown earlier in this thread):

    INSERT INTO `tblawningprojections` (`proId`, `proawningId`, `prowidth`, `proarms`) VALUES
    (1, 1, 1.78, 1.50),
    (2, 1, 2.30, 2.00),
    (3, 1, 2.73, 2.50),
    (4, 1, 3.21, 3.00),
    (5, 2, 2.03, 1.50),
    (6, 2, 2.55, 2.00),
    (7, 2, 2.98, 2.50),
    (8, 2, 3.46, 3.00),
    (9, 3, 2.65, 1.50),
    (10, 3, 2.65, 2.00),
    (11, 3, 2.98, 2.50),
    (12, 3, 3.46, 3.00),
    (13, 4, 2.03, 1.50),
    (14, 4, 2.55, 2.00),
    (15, 4, 2.98, 2.50),
    (16, 4, 3.46, 3.00),
    (17, 4, 3.97, 3.50),
    (18, 4, 4.22, 3.75);

    the system should return (bear in mind it's looking for "not greater than" 2.4 in the width column):

    (3, 1, 2.73, 2.50)
    (6, 2, 2.55, 2.00)
    (9, 3, 2.65, 1.50)
    (14, 4, 2.55, 2.00)

    these four records show are all the correct "up to" width value (remember our width was 2.4m) - therefore the possible arm lengths are (for the 4 awning types in my table):

    2.50
    2.00
    1.50
    2.00

    What I'd like to show (in my form select list) is just one entry for any particular arm length - so using the data above that would be (i.e. only one 2.50 entry):

    1.50
    2.00
    2.50

    So if your awning width is 2.4m then you can choose from 1.50, 2.00 or 2.50m arm projections.

    I hope that helps explain the process.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    would you please explain the "not greater than" part

    apparently 2.73, 2.55, 2.65 and 2.55 are "not greater than" 2.4 ????

    if so, wouldn't 3.46 also be "not greater than" 2.4 ????

    and why would you want multiple awningids elegible for return?

    hopefully you can see why i am confoosled
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Sorry about all the confusion.

    To find the "arm projection" for a particular width you find the entry that is greater than (or equals) your desired width. It's probably easier to think of the width column (prowidth) as being a "between" the previous value and this value, so (in plain english), for proawningId 1 you'd say:

    find the arm length for each awning type (proawningId) where the width is:
    between 0.00 - 1.78 or
    between 1.78 - 2.30 or
    between 2.30 - 2.73 or
    between 2.73 - 3.21

    and for our 2.4m width that would be record 3 and the arm length is 2.5m. And you'd then do this for all awning types (proawningId).

    Maybe I should amend the table to change the width column to "to-width" and add a "from-width" column as well?

    Also, as mentioned earlier, the awningId reference can be ignored - my original SQL should have read:

    SELECT proarms, proId, proawningId, prowidth FROM ukawnings.tblawningprojections WHERE prowidth >= (this is the width entered on the form) GROUP BY proawningId ORDER BY proarms ASC

    this SQL almost works except that it lists the correct arm length for each awning type, i.e.
    1.5
    2.0
    2.0
    2.5

    but I just want Distinct arm lengths, i.e.:
    1.5
    2.0
    2.5

    Again, sorry for the confusion - this is one of those things that's difficuly to explain without seeing the whole picture.


Bookmarks

Posting Permissions

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