Professional Web Applications Themes

Interesting SQL query requirement for <SELECT> menu - MySQL

Hi All Wondered if you could help me with the below query. I have 1 simple table called STOCKCATS that consists of 2 fields. These fields are called CATID and LEVEL. The contents of this table are as follows: CATID LEVEL cat01 <nothing> cat02 <nothing> cat03 cat01 cat04 <nothing> cat05 cat01 cat06 cat02 cat07 cat04 etc.. etc... The way this table works is that I have an ASP page that allows the user to create a stock category at 2 levels, category level and sub-category level. When I file the entered data into the table, if the user has chosen ...

  1. #1

    Default Interesting SQL query requirement for <SELECT> menu

    Hi All

    Wondered if you could help me with the below query.

    I have 1 simple table called STOCKCATS that consists of 2 fields.

    These fields are called CATID and LEVEL.

    The contents of this table are as follows:

    CATID LEVEL
    cat01 <nothing>
    cat02 <nothing>
    cat03 cat01
    cat04 <nothing>
    cat05 cat01
    cat06 cat02
    cat07 cat04
    etc.. etc...

    The way this table works is that I have an ASP page that allows the user to
    create a stock category at 2 levels, category level and sub-category level.

    When I file the entered data into the table, if the user has chosen to
    create a category level stock category then the LEVEL field is left blank
    and if they chose to create a sub-category level category then I post the
    relevant category level stock category code in the LEVEL field. For
    example, in the above list cat01 is a category level stock category and
    cat05 is a sub-category as it is a sub-category of cat01.

    My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
    but instead of it being a straightforward 'select catid from stockcats order
    by catid', I want to group this list into some kind of order, eg:

    instead of:

    cat01 <nothing> << I need to bring back this 2nd column so that I can
    do a simple IF THEN in asp to indent sub-cats
    cat02 <nothing>
    cat03 cat01
    cat04 <nothing>
    cat05 cat01
    cat06 cat02
    cat07 cat04

    I would like

    cat01 <nothing> << ditto
    cat03 cat01
    cat05 cat01
    cat02 <nothing>
    cat06 cat02
    cat04 <nothing>
    cat07 cat04

    Do you know if this is possible in pure SQL (I must confess that I'm using
    MySQL, but I would have thought the SQL syntax would be the same if it is
    possible) or a combo of ASP & SQL?

    Thanks

    Robbie



    Astra Guest

  2. #2

    Default Re: Interesting SQL query requirement for <SELECT> menu

    Astra wrote:
    > My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
    > but instead of it being a straightforward 'select catid from stockcats order
    > by catid', I want to group this list into some kind of order, eg:
    >
    > I would like
    >
    > cat01 <nothing> << ditto
    > cat03 cat01
    > cat05 cat01
    > cat02 <nothing>
    > cat06 cat02
    > cat04 <nothing>
    > cat07 cat04
    Here's how I'd do it:

    SELECT s.catid, s.level
    FROM stockcats AS s
    ORDER BY COALESCE(s.level, s.catid), s.catid

    I'm assuming that by <nothing> you mean that the field has a NULL state.
    COALESCE is a standard SQL function that returns its first non-null
    argument. I list a secondary sort field so that cat03 and cat05 will be
    sorted correctly, since both have the same level field.

    By the way, if you ever go to three levels, or unlimited levels, of
    subcategorization, you'll need to store trees. It gets a lot harder to
    manipulate heirarchies in SQL by storing simply the parent-child
    relationship as you are doing. I recommend Joe Celko's book "SQL for
    Smarties"; it has a chapter on manipulating heirarchies and trees in SQL.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Interesting SQL query requirement for <SELECT> menu

    Genius!!

    Many thanks.

    Rgds Robbie

    "Bill Karwin" <billkarwin.com> wrote in message
    news:dko5n102m59enews4.newsguy.com...
    Astra wrote:
    > My query is that I want to populate a simple HTML <SELECT> menu (using
    > ASP),
    > but instead of it being a straightforward 'select catid from stockcats
    > order
    > by catid', I want to group this list into some kind of order, eg:
    >
    > I would like
    >
    > cat01 <nothing> << ditto
    > cat03 cat01
    > cat05 cat01
    > cat02 <nothing>
    > cat06 cat02
    > cat04 <nothing>
    > cat07 cat04
    Here's how I'd do it:

    SELECT s.catid, s.level
    FROM stockcats AS s
    ORDER BY COALESCE(s.level, s.catid), s.catid

    I'm assuming that by <nothing> you mean that the field has a NULL state.
    COALESCE is a standard SQL function that returns its first non-null
    argument. I list a secondary sort field so that cat03 and cat05 will be
    sorted correctly, since both have the same level field.

    By the way, if you ever go to three levels, or unlimited levels, of
    subcategorization, you'll need to store trees. It gets a lot harder to
    manipulate heirarchies in SQL by storing simply the parent-child
    relationship as you are doing. I recommend Joe Celko's book "SQL for
    Smarties"; it has a chapter on manipulating heirarchies and trees in SQL.

    Regards,
    Bill K.


    Astra Guest

Similar Threads

  1. help with a SELECT query
    By halex2000 in forum MySQL
    Replies: 2
    Last Post: July 30th, 08:34 PM
  2. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  3. select query help
    By Abul Hasan Lakhani in forum MySQL
    Replies: 8
    Last Post: October 15th, 07:04 PM
  4. Using < and > in a select query
    By Grynch in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 18th, 03:55 AM
  5. to get the nth record meeting the query requirement
    By Michelle in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: June 30th, 02:25 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139