Professional Web Applications Themes

What looks like a basic SQL query still not resolved - MySQL

ARRRRRRRGGGGGHHHHH!! Please can you help, I'm going round the bend with this. I have a simple and small table called STOCKCATS, which I need to query to get back a dataset in a particular order, but although it looks simple I can't get it to work. My table schema plus sample data to see the problem is as follows: DROP TABLE IF EXISTS `STOCKCATS`; CREATE TABLE `STOCKCATS` ( `CATID` varchar(30) NOT NULL default '', `LEVEL` varchar(30) default NULL, PRIMARY KEY (`CATID`), KEY `indxCATEGORYID` (`CATID`) ); INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES ('A001',''), ('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001'), ('PCHW01',''), ('MHW01',''), ('FD01',''), ('ELEC01',''), ('MHW02','MHW01'); ...

  1. #1

    Default What looks like a basic SQL query still not resolved

    ARRRRRRRGGGGGHHHHH!!

    Please can you help, I'm going round the bend with this.

    I have a simple and small table called STOCKCATS, which I need to query to
    get back a dataset in a particular order, but although it looks simple I
    can't get it to work. My table schema plus sample data to see the problem
    is as follows:

    DROP TABLE IF EXISTS `STOCKCATS`;
    CREATE TABLE `STOCKCATS` (
    `CATID` varchar(30) NOT NULL default '',
    `LEVEL` varchar(30) default NULL,

    PRIMARY KEY (`CATID`),
    KEY `indxCATEGORYID` (`CATID`)
    );

    INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    ('A001',''),
    ('A002','A001'),
    ('A003','A001'),
    ('A004','A001'),
    ('A005','A001'),
    ('PCHW01',''),
    ('MHW01',''),
    ('FD01',''),
    ('ELEC01',''),
    ('MHW02','MHW01');
    INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    ('MHW03','MHW01'),
    ('MHW04','MHW01'),
    ('MHW05','MHW01'),
    ('PCHW02','PCHW01'),
    ('PCHW03','PCHW01'),
    ('PCHW04','PCHW01'),
    ('PCHW05','PCHW01'),
    ('PCSW01',''),
    ('MSW01',''),
    ('C001',''),
    ('C002','C001'),
    ('C003','C001'),
    ('MV',''),
    ('SUZ','MV'),
    ('ALF','MV'),
    ('PLASMA','ELEC01'),
    ('T01','ELEC01'),
    ('HEATING',''),
    ('RAD','HEATING'),
    ('P01',''),
    ('B01','P01'),
    ('BB','HEATING'),
    ('FS','HEATING'),
    ('WM','HEATING'),
    ('AEROSOL',''),
    ('SOLVENTS','AEROSOL'),
    ('DGC','');
    INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    ('DGXWINDOWS','DGC'),
    ('DGXEXTRA','DGC'),
    ('DGXCON','DGC');

    As you can see from the table structure, this table consists of 2 field
    values. The 1st is the category code and the 2nd is the level is at. If a
    catid has a level of nothing, eg '', then it means that it is a root level
    category. If a catid has a another cat's catid in it's level, eg B01 has
    P01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.

    All I want to do is query this table and bring back the data so that
    alphabetically it goes root level cat A1, then all the sub-cats for this
    root level, then root level A2, then all sub-cats for this root level and so
    on. An example using the above would be as follows:

    ^ ^ A to G of root level cats plus their sub-cats....

    HEATING << root level
    BB << sub-cat of heating
    FS << sub-cat of heating
    WM << sub-cat of heating

    \/ \/ I to Z of root level cats plus their sub-cats....

    A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),
    CATID and I thought this had done it, but I was looking at the ('A001',''),
    ('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')
    entries as these naturally fell into place. If you use this order command
    on the above you will see that ('P01','') and it's associated ('B01','P01')
    sub-cat just don't come together.

    Does any body have any ideas?

    Thanks

    Laphan



    Laphan Guest

  2. #2

    Default Re: What looks like a basic SQL query still not resolved

    It's not so bad. Question: Can a category belong to more than one level? And
    thanks for posting the DDL. It makes it much easier.

    "Laphan" <infoSpamMeNot.co.uk> wrote in message
    news:11nooco13bv9db2corp.supernews.com...
    > ARRRRRRRGGGGGHHHHH!!
    >
    > Please can you help, I'm going round the bend with this.
    >
    > I have a simple and small table called STOCKCATS, which I need to query to
    > get back a dataset in a particular order, but although it looks simple I
    > can't get it to work. My table schema plus sample data to see the problem
    > is as follows:
    >
    > DROP TABLE IF EXISTS `STOCKCATS`;
    > CREATE TABLE `STOCKCATS` (
    > `CATID` varchar(30) NOT NULL default '',
    > `LEVEL` varchar(30) default NULL,
    >
    > PRIMARY KEY (`CATID`),
    > KEY `indxCATEGORYID` (`CATID`)
    > );
    >
    > INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    > ('A001',''),
    > ('A002','A001'),
    > ('A003','A001'),
    > ('A004','A001'),
    > ('A005','A001'),
    > ('PCHW01',''),
    > ('MHW01',''),
    > ('FD01',''),
    > ('ELEC01',''),
    > ('MHW02','MHW01');
    > INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    > ('MHW03','MHW01'),
    > ('MHW04','MHW01'),
    > ('MHW05','MHW01'),
    > ('PCHW02','PCHW01'),
    > ('PCHW03','PCHW01'),
    > ('PCHW04','PCHW01'),
    > ('PCHW05','PCHW01'),
    > ('PCSW01',''),
    > ('MSW01',''),
    > ('C001',''),
    > ('C002','C001'),
    > ('C003','C001'),
    > ('MV',''),
    > ('SUZ','MV'),
    > ('ALF','MV'),
    > ('PLASMA','ELEC01'),
    > ('T01','ELEC01'),
    > ('HEATING',''),
    > ('RAD','HEATING'),
    > ('P01',''),
    > ('B01','P01'),
    > ('BB','HEATING'),
    > ('FS','HEATING'),
    > ('WM','HEATING'),
    > ('AEROSOL',''),
    > ('SOLVENTS','AEROSOL'),
    > ('DGC','');
    > INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    > ('DGXWINDOWS','DGC'),
    > ('DGXEXTRA','DGC'),
    > ('DGXCON','DGC');
    >
    > As you can see from the table structure, this table consists of 2 field
    > values. The 1st is the category code and the 2nd is the level is at. If
    a
    > catid has a level of nothing, eg '', then it means that it is a root level
    > category. If a catid has a another cat's catid in it's level, eg B01 has
    > P01, then it is a sub-category of this category, eg B01 is a sub-cat of
    P01.
    >
    > All I want to do is query this table and bring back the data so that
    > alphabetically it goes root level cat A1, then all the sub-cats for this
    > root level, then root level A2, then all sub-cats for this root level and
    so
    > on. An example using the above would be as follows:
    >
    > ^ ^ A to G of root level cats plus their sub-cats....
    >
    > HEATING << root level
    > BB << sub-cat of heating
    > FS << sub-cat of heating
    > WM << sub-cat of heating
    >
    > \/ \/ I to Z of root level cats plus their sub-cats....
    >
    > A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),
    > CATID and I thought this had done it, but I was looking at the
    ('A001',''),
    > ('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')
    > entries as these naturally fell into place. If you use this order command
    > on the above you will see that ('P01','') and it's associated
    ('B01','P01')
    > sub-cat just don't come together.
    >
    > Does any body have any ideas?
    >
    > Thanks
    >
    > Laphan
    >
    >
    >

    Rich Ryan Guest

  3. #3

    Default Re: What looks like a basic SQL query still not resolved

    Hi Guys

    Many thanks for your replies. I must be honest, I've been a naughty poster
    and posted this in an ASP ng as well. A genius called David helped me with
    the answer, so just for your ref the solution is as follows:

    SELECT catid, level
    FROM stockcats
    ORDER BY COALESCE(NULLIF(level,''),catid),catid;

    Rgds Laphan



    "Rich Ryan" <ryanrjsbcglobal.net> wrote in message
    news:p78ff.486$4o7.406newssvr24.news.prodigy.net. ..
    It's not so bad. Question: Can a category belong to more than one level? And
    thanks for posting the DDL. It makes it much easier.

    "Laphan" <infoSpamMeNot.co.uk> wrote in message
    news:11nooco13bv9db2corp.supernews.com...
    > ARRRRRRRGGGGGHHHHH!!
    >
    > Please can you help, I'm going round the bend with this.
    >
    > I have a simple and small table called STOCKCATS, which I need to query to
    > get back a dataset in a particular order, but although it looks simple I
    > can't get it to work. My table schema plus sample data to see the problem
    > is as follows:
    >
    > DROP TABLE IF EXISTS `STOCKCATS`;
    > CREATE TABLE `STOCKCATS` (
    > `CATID` varchar(30) NOT NULL default '',
    > `LEVEL` varchar(30) default NULL,
    >
    > PRIMARY KEY (`CATID`),
    > KEY `indxCATEGORYID` (`CATID`)
    > );
    >
    > INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    > ('A001',''),
    > ('A002','A001'),
    > ('A003','A001'),
    > ('A004','A001'),
    > ('A005','A001'),
    > ('PCHW01',''),
    > ('MHW01',''),
    > ('FD01',''),
    > ('ELEC01',''),
    > ('MHW02','MHW01');
    > INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    > ('MHW03','MHW01'),
    > ('MHW04','MHW01'),
    > ('MHW05','MHW01'),
    > ('PCHW02','PCHW01'),
    > ('PCHW03','PCHW01'),
    > ('PCHW04','PCHW01'),
    > ('PCHW05','PCHW01'),
    > ('PCSW01',''),
    > ('MSW01',''),
    > ('C001',''),
    > ('C002','C001'),
    > ('C003','C001'),
    > ('MV',''),
    > ('SUZ','MV'),
    > ('ALF','MV'),
    > ('PLASMA','ELEC01'),
    > ('T01','ELEC01'),
    > ('HEATING',''),
    > ('RAD','HEATING'),
    > ('P01',''),
    > ('B01','P01'),
    > ('BB','HEATING'),
    > ('FS','HEATING'),
    > ('WM','HEATING'),
    > ('AEROSOL',''),
    > ('SOLVENTS','AEROSOL'),
    > ('DGC','');
    > INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
    > ('DGXWINDOWS','DGC'),
    > ('DGXEXTRA','DGC'),
    > ('DGXCON','DGC');
    >
    > As you can see from the table structure, this table consists of 2 field
    > values. The 1st is the category code and the 2nd is the level is at. If
    a
    > catid has a level of nothing, eg '', then it means that it is a root level
    > category. If a catid has a another cat's catid in it's level, eg B01 has
    > P01, then it is a sub-category of this category, eg B01 is a sub-cat of
    P01.
    >
    > All I want to do is query this table and bring back the data so that
    > alphabetically it goes root level cat A1, then all the sub-cats for this
    > root level, then root level A2, then all sub-cats for this root level and
    so
    > on. An example using the above would be as follows:
    >
    > ^ ^ A to G of root level cats plus their sub-cats....
    >
    > HEATING << root level
    > BB << sub-cat of heating
    > FS << sub-cat of heating
    > WM << sub-cat of heating
    >
    > \/ \/ I to Z of root level cats plus their sub-cats....
    >
    > A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),
    > CATID and I thought this had done it, but I was looking at the
    ('A001',''),
    > ('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')
    > entries as these naturally fell into place. If you use this order command
    > on the above you will see that ('P01','') and it's associated
    ('B01','P01')
    > sub-cat just don't come together.
    >
    > Does any body have any ideas?
    >
    > Thanks
    >
    > Laphan
    >
    >
    >


    Laphan Guest

Similar Threads

  1. basic query question
    By Mike in forum MySQL
    Replies: 6
    Last Post: May 9th, 05:22 PM
  2. Basic query speed question
    By cobramichelle in forum Coldfusion Database Access
    Replies: 1
    Last Post: December 23rd, 05:48 PM
  3. The remote name could not be resolved
    By Mark Shaw in forum ASP.NET Web Services
    Replies: 2
    Last Post: September 28th, 06:46 AM
  4. Dark basic pro VS blitz basic 3D VS 3d game studio VS shockwave VS jamajic
    By kavelle webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 1
    Last Post: January 29th, 09:53 AM

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