Microsoft SQL Server forum - What looks like a basic SQL query still not resolved

Author

What looks like a basic SQL query still not resolved

Laphan

2005-11-17, 9:23 am

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

David Portas

2005-11-17, 9:23 am

Replied in microsoft.public.sqlserver.programming.

Please do not multi-post.

--
David Portas
SQL Server MVP
--

Andy Kent

2005-11-17, 9:23 am

Oracle has a CONNECT BY clause to do recursive joins like this - not a
help to you as such, but the point is that they have found it necessary
to have a specific feature to accomplish what you're trying to do. They
used to call the technique "tree walking". Don't know if SQL Server has
something similar ...

Branco Medeiros

2005-11-17, 1:23 pm

Laphan wrote:
> ARRRRRRRGGGGGHHHHH!!

<snip>
> INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
> ('A001',''),
> ('A002','A001'),
> ('A003','A001'),
> ('A004','A001'),
> ('A005','A001'),
> ('PCHW01',''),
> ('MHW01',''),
> ('FD01',''),
> ('ELEC01',''),
> ('MHW02','MHW01');
<snip>
> 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.
<snip>

Have you tried:

SELECT * FROM STOCKCATS
ORDER BY
CASE WHEN level='' THEN catid + '-0'
ELSE level + '-1'
END, catid

hth,

Regards,

Branco.

--CELKO--

2005-11-17, 8:24 pm

Get a copy of TREES & HIERARCHIES IN SQL for several ways to do this.
Or Google "nested set model" for one way.

Laphan

2005-11-18, 7:23 am

Hi Guys

Many thanks for your replies. I do apologise for the multi-posts, but some
of my queries just seem cover a couple of ngs.

The solution that David posted in another ng worked perfectly.

Many thanks once again.

Rgds Laphan

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1132258928.055591.220620@g47g2000cwa.googlegroups.com...
Get a copy of TREES & HIERARCHIES IN SQL for several ways to do this.
Or Google "nested set model" for one way.

David Portas

2005-11-18, 9:23 am

Laphan wrote:
> Hi Guys
>
> Many thanks for your replies. I do apologise for the multi-posts, but some
> of my queries just seem cover a couple of ngs.
>

If you really must hit several groups then cross-post, don't
multi-post. If you don't know how to cross-post then refer to the
documentation for your news client. Most allow you to specify more than
one group on the TO: line.

--
David Portas
SQL Server MVP
--

Sponsored Links