Professional Web Applications Themes

Complicated "CASE WHEN" query for Dreamweaver Recordset - MySQL

Hello, I have a table called "categories_ctg" with the following fields: id_ctg, int(11), auto_increment idctg_ctg, int(11)(can be NULL) name_ctg, varchar(100) longname_ctg, varchar(255) order_ctg, int(11) The table is stored for categories, for instance 1, NULL, hardware, hardware, 1 2,1,computers,computer systems,2 3,1,printers,printers,3 4,NULL,software,software,4 I use this to navigate on a website. What I want is to show the above al follows: hardware > computers hardware > printers software The query I've tried is: SELECT subcategories_ctg.id_ctg, CASE WHEN categories_ctg.name_ctg IS NULL THEN subcategories_ctg.name_ctg ELSE categories_ctg.name_ctg, ' > ', subcategories_ctg.name_ctg END as name_ctg FROM categories_ctg, subcategories_ctg LEFT JOIN categories_ctg ON subcategories_ctg.idctg_ctg=categories_ctg.id_ctg ORDER BY name_ctg ...

  1. #1

    Default Complicated "CASE WHEN" query for Dreamweaver Recordset

    Hello,

    I have a table called "categories_ctg" with the following fields:
    id_ctg, int(11), auto_increment
    idctg_ctg, int(11)(can be NULL)
    name_ctg, varchar(100)
    longname_ctg, varchar(255)
    order_ctg, int(11)

    The table is stored for categories, for instance
    1, NULL, hardware, hardware, 1
    2,1,computers,computer systems,2
    3,1,printers,printers,3
    4,NULL,software,software,4

    I use this to navigate on a website. What I want is to show the above al
    follows:

    hardware > computers
    hardware > printers
    software

    The query I've tried is:
    SELECT subcategories_ctg.id_ctg, CASE WHEN categories_ctg.name_ctg IS NULL
    THEN subcategories_ctg.name_ctg ELSE categories_ctg.name_ctg, ' > ',
    subcategories_ctg.name_ctg END as name_ctg
    FROM categories_ctg, subcategories_ctg LEFT JOIN categories_ctg ON
    subcategories_ctg.idctg_ctg=categories_ctg.id_ctg
    ORDER BY name_ctg

    But this gives the following error on a browser output:
    "There is something wrong in the syntax at ' ' > ',
    subcategories_ctg.name_ctg END as name_ctg FROM categori' at line 1.

    I am using Dreamweaver (8.02) to edit the queries, where get a MySQL
    error:#1064.
    If anybody could tell me how to get the desired result, I'll be grateful,
    since I'm not a real specialist and I've been trying this for 3 days now :(.

    Greetings,
    HJA



    hja Guest

  2. #2

    Default Re: Complicated "CASE WHEN" query for Dreamweaver Recordset

    hja wrote:
    > Hello,
    >
    > I have a table called "categories_ctg" with the following fields:
    > id_ctg, int(11), auto_increment
    > idctg_ctg, int(11)(can be NULL)
    > name_ctg, varchar(100)
    > longname_ctg, varchar(255)
    > order_ctg, int(11)
    >
    > The table is stored for categories, for instance
    > 1, NULL, hardware, hardware, 1
    > 2,1,computers,computer systems,2
    > 3,1,printers,printers,3
    > 4,NULL,software,software,4
    >
    > I use this to navigate on a website. What I want is to show the above
    > al follows:
    >
    > hardware > computers
    > hardware > printers
    > software
    >
    > The query I've tried is:
    > SELECT subcategories_ctg.id_ctg, CASE WHEN categories_ctg.name_ctg IS
    > NULL THEN subcategories_ctg.name_ctg ELSE categories_ctg.name_ctg, '
    > > ', subcategories_ctg.name_ctg END as name_ctg
    > FROM categories_ctg, subcategories_ctg LEFT JOIN categories_ctg ON
    > subcategories_ctg.idctg_ctg=categories_ctg.id_ctg
    > ORDER BY name_ctg
    >
    > But this gives the following error on a browser output:
    > "There is something wrong in the syntax at ' ' > ',
    > subcategories_ctg.name_ctg END as name_ctg FROM categori' at line 1.
    >
    > I am using Dreamweaver (8.02) to edit the queries, where get a MySQL
    > error:#1064.
    > If anybody could tell me how to get the desired result, I'll be
    > grateful, since I'm not a real specialist and I've been trying this
    > for 3 days now :(.
    > Greetings,
    > HJA
    Take a look at
    [url]http://dev.mysql.com/tech-resources/articles/hierarchical-data.html[/url]


    Paul Lautman Guest

Similar Threads

  1. Accented letters in "export Recordset As XML"
    By fastmax in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: September 12th, 08:51 PM
  2. ColdFusion+cfquery+Oracle+CLOB+"Query of Query"
    By ferse in forum Coldfusion Database Access
    Replies: 0
    Last Post: April 25th, 01:28 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. Replies: 0
    Last Post: July 11th, 03:27 AM
  5. ERROR-- Unable to open file "../Recordset.js"
    By Zoin Amir in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 11:08 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